Solved

SQL Query Help

Posted on 2009-05-15
6
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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