mardi 23 février 2016

SQL 2005 returning unique results with subquery

I have a database Table a (EMAILS) where EmailID is the Primary Key

EmailID     Email_To     Email_From    Email_Subject    Email_Registered    Email_Read

If a user creates an email it is registered in this table.

For example, the user "Dave" who has id 3 sends an email to "John" who has id 4

So this would give

EmailID     Email_To     Email_From    Email_Subject    Email_Registered    Email_Read
   10          4             3              TEST        2/23/2016 11:00       False

To return results I do this select (joining the user profile database)

SELECT PROFILE_1.SellerID, PROFILE_1.Seller_UserName, EMAILS.EmailID, EMAILS.Email_From, EMAILS.Email_To, EMAILS.Email_Subject, 
                  EMAILS.Email_Registered, EMAILS.Email_Read,
                      (SELECT Seller_UserName AS Epr2
                        FROM PROFILE
                        WHERE (SellerID = EMAILS.Email_To)) AS Expr2
FROM PROFILE AS PROFILE_1 LEFT OUTER JOIN
                  EMAILS ON EMAILS.Email_From = PROFILE_1.SellerID
WHERE (EMAILS.Email_From IS NOT NULL) AND (PROFILE_1.Seller_UserName = 'Dave')
ORDER BY EMAILS.Email_Registered DESC

So John Replies to Dave's email and it goes into the EMAILS_THREAD table and is registered as

EmailThreadID     EmailID     Email_To     Email_From     Email_Registered     Email_Read
      1              10          3             4           2/23/2016 11:05       False

What I am trying to do is a select that

SELECTS from EMAILS where Email_From is from Dave and return in the results the top 1 result from EMAIL_THREADS that is sent to Dave (based on Email_Registered) with the same EmailID as the EMAILS.EmailID if there is a entry in EMAIL_THREADS.

So in other words return the result of the EMAIL table and latest corresponding result in the EMAIL_THREADS table if there is one.

I hope this makes sense.

I've tried a ton of combinations and I can't figure this out.

At first I thought it was a subquery or a join or a group by...but i can't seem to nail the select and how it is structured.

Looking for some help.

Aucun commentaire:

Enregistrer un commentaire