Solved

SQL Query Help

Posted on 2009-05-15
6
172 Views
Last Modified: 2012-05-07
I have two tables: User table and Order table

Let say user table has following records

usera1,emaila
usera2,emaila
userb,emailb
userc1,emailc
userc2,emailc
userd,emaild

Order table has following two records
usera1,emaila
userb,emailb
userc1,emailc
userc2,emailc


As you see there are two user accounts for each emaila and emailc
However usera2 using emaila is just duplicate account with no associated record in order table.
How can I get SQL query that will give me list of such duplicate accounts?
Please note userd is not in the order table but there us no duplicate in the user table so it should not be included in query result.

Basically query should result user account that is not in order table and that has another account using same email.
0
Comment
Question by:shwekhaw
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:mahome
ID: 24393101
Try this:

select * from user u
where u.user_name not in (select o.user_name from order o)
and u.email in (
  select u2.email from user u2
  group by u2.email
  having count(*) > 1
)

Open in new window

0
 

Author Comment

by:shwekhaw
ID: 24393204
It freezes up, User table has about 35000 records. Orde table has over 50000 records
I am not sure the problem is table size or syntax.
0
 

Author Comment

by:shwekhaw
ID: 24393285
I tried running just the following and it is ok.
select * from user u
where u.user_name not in (select o.user_name from order o)

I also try running just
select u2.email from user u2
  group by u2.email
  having count(*) > 1

It seems ok too. But if I combine into one syntax, it is taking forever.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:shwekhaw
ID: 24393388
The part that is not working or taking so long to execute is

select * from user u
where u.email in (
  select u2.email from user u2
  group by u2.email
  having count(*) > 1
)

How can I get arounf this?
0
 
LVL 10

Expert Comment

by:mahome
ID: 24393396
do you have an index on email?
0
 
LVL 4

Accepted Solution

by:
bleach77 earned 500 total points
ID: 24393651
This SQL do:
1.  Get result of duplicate email.
2.  Get the user id associated with the email.
3.  Eliminate the user that is in "order" table.

I don't really get what you really want. But this is based on mahome answer.
SELECT u2.userid, u2.email
FROM (SELECT email FROM user GROUP BY email HAVING count(userid) > 1) u LEFT JOIN user u2 ON u.email=u2.email
WHERE u2.userid NOT IN (SELECT userid FROM order)

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question