0

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.

Here is my last attempt

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 CROSS JOIN
                      (SELECT TOP (1) EMAILS_THREAD.Email_From, EMAILS_THREAD.Email_To, EMAILS_THREAD.Email_Registered, EMAILS_THREAD.Email_Read
                        FROM EMAILS_THREAD LEFT OUTER JOIN
                                               EMAILS AS EMAILS_1 ON EMAILS_THREAD.EmailID = EMAILS_1.EmailID) AS derivedtbl_1
WHERE (EMAILS.Email_From IS NOT NULL) AND (PROFILE_1.Seller_UserName = 'Dave')
ORDER BY EMAILS.Email_Registered DESC

But it's not returning anything from EMAILS_THREADS at all.

Steve
  • 75
  • 1
  • 9
  • It is a sub-query. Please post your attempt with a sub-query so that we can debug it. – Tab Alleman Feb 23 '16 at 13:52
  • Hi Tab, using "Dave" to test. – Steve Feb 23 '16 at 14:02
  • Sorry...jepp...I guess you see straight away I am not too experienced here. :) – Steve Feb 23 '16 at 14:06
  • Well you don't have any columns from EMAILS_THREADS (derivedtbl_1) in your Select list. You are only selecting columns from PROFILE_1 and EMAILS. – Tab Alleman Feb 23 '16 at 14:09
  • Haven't been able to find a solution as yet Tab. I'm not getting the result set from the cte select kindly offered by TheGameiswar. – Steve Feb 23 '16 at 16:02
  • I don't want to get involved in debugging his answer. If you post your latest attempt in your question (fixing the issues I mentioned above), I will look at it. – Tab Alleman Feb 23 '16 at 16:33

2 Answers2

0

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.Hope this helps

   with cte
as
(select
* from emails 
where email_from=3
)
select * from cte mt --only for dave
outer apply
(
select top 1* from 
emial_threads st where mt.mail_from=st.mail_to ---send to dave
and mt.emailid=st.emailid) b
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • Hi TheGameiswar! I'm getting Incorrect syntax near keyword CROSS in management studio. Any ideas? – Steve Feb 23 '16 at 14:20
  • try now,i missed derived table alias – TheGameiswar Feb 23 '16 at 14:25
  • Hi, I was just reading http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join and tried to add an alias (as you have with 'b')...but its the same Incorrect syntax near keyword 'CROSS' and now as well incorrect syntax near 'b'. – Steve Feb 23 '16 at 14:29
  • Edited plz see again – TheGameiswar Feb 23 '16 at 14:33
  • Hi thanks...but I'm a bit confused. I don't get the statement " with cte" ? – Steve Feb 23 '16 at 14:37
  • CTE is a like a view which stores the definition of our SQL for immediate execution purposes – TheGameiswar Feb 23 '16 at 14:42
  • OK...Its is returning a combined result (that's great!) but it is only returning a result when there is an entry in EMAIL_THREAD with Email_To The problem is as I mentioned that every EMAIL entry may not have an entry in EMAIL_THREAD. – Steve Feb 23 '16 at 14:44
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104308/discussion-between-thegameiswar-and-steve). – TheGameiswar Feb 23 '16 at 14:46
  • seems like the query (Top 1) may be the problem? In that only 1 result is ever returned instead of all results and only the top 1 for each EmailID in Emails_Threads with matching EmailID? – Steve Feb 23 '16 at 14:47
0

Haven't been able to find a solution to this so I am paying a SQL professional to resolve this for me. Thanks for the input from Tab and The Gameiswar. Much appreciated.

Steve
  • 75
  • 1
  • 9