Trouble using JOINS, GROUP BY

[Armand817]

I have 2 tables:
Users --> email, UID (pk)
Orders --> Ordernumber, UID (pk)

I am trying to clean up the database. Some users have created multiple accounts in the database. Each account they create will generate a new UID but will have their same email address. I need to find which email addresses have multiple UIDs and which of those UIDs have an Ordernumber associated with it (for example, an account could have been created but there may not be an Ordernumber associated with that account). So in the end I hope to see an email address with all of its UIDs and do those UIDs have an Ordernumber associated with it…yes or no or some other designation. Does this seem doable, reasonable? I have very limited experience. I have been working with GROUP BY, JOINS, VIEWS, etc., and have been getting nowhere. Any help will be appreciated.

The following code shows me which emails have multiple accounts but I still need to display the UIDs for each account and whether or not there is an ordernumber associated with it...

Select EMAIL, COUNT(EMAIL) AS eCNT
 FROM USERS
 GROUP BY EMAIL
 HAVING (COUNT(EMAIL) > 1)
 

[Delerna]

SELECT b.UID,b.Email,c.OrderNumber
FROM
(   Select EMAIL 
    FROM USERS GROUP BY EMAIL 
    HAVING COUNT(EMAIL) > 1
)a
JOIN USERS b on a.Email=b.Email
LEFT JOIN Orders c on b.UID=c.UID
 

For all duplicated emails, order number will be null for UID's that don't have any orders on them


or

SELECT b.UID,b.Email,count(c.OrderNumber) as NumOrds
FROM
(   Select EMAIL 
    FROM USERS GROUP BY EMAIL 
    HAVING COUNT(EMAIL) > 1
)a
JOIN USERS b on a.Email=b.Email
LEFT JOIN Orders c on b.UID=c.UID
GROUP BY b.UID,b.Email
 

will tell you how many orders each UID has


Hope I didn't make any typo's there :)


The query inside the brackets is called a sub query or derived table

[Armand817]

Thank you so much. Both of your solutions worked perfectly! I appreciate your help on this. I can see I need to spend a lot more time on derived Tables and Joins...