Solved

SQL Query Help

Posted on 2009-05-15
6
170 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to create a delete query to delete entire records 2 33
SQL query bug 3 28
Help writing a query 6 71
how to install/upgrade the Blitz responder kit 8 22
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now