Solved

SQL Query Help

Posted on 2009-05-15
6
174 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

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 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
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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