Solved

SQL Query Help

Posted on 2009-05-15
6
169 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:shwekhaw
Comment Utility
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
Comment Utility
do you have an index on email?
0
 
LVL 4

Accepted Solution

by:
bleach77 earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

11 Experts available now in Live!

Get 1:1 Help Now