samedi 2 avril 2016

SQLITE query to join two of same tables with different conditions

I have this SQL table called messages which has the details like following;

id, sender, recipient, message, timestamp

"1","10204456572654160","10208819391147662","Hi.. : What are you up to?","1459541723279"
"2","10204456572654160","10208819391147662","Got my test message?","1459541749818"
"3","10208819391147662","10204456572654160","This is my message","1459611679108"
"4","10208819391147662","10204456572654160","And another message","1459611735455"
"5","10204456572654160","10208819391147662","And I reply like this","1459611758570"
"6","10153775515332771","10208819391147662","It's me.. Syco !!","1459611900348"
"7","10153775515332771","10208819391147662","You there?","1459611900350"
"8","10208819391147662","10153775515332771","Yes.. What's upp..","1459611900380"
"9","10204464403169266","10208819391147662","How're you doin?","1459612000666"

Now, I want to pass a value to my function e.g. 10208819391147662 and to show result like following columns;

  1. Unique value of sender/recipient which is NOT 10208819391147662
  2. The last message between sender/recipient which is NOT 10208819391147662 AND sender/recipient which is 10208819391147662.
  3. The timestamp of the 2nd (message).

In order to achieve the following, I have this sql;

SELECT s1.*, max(c2.message), max(c2.timestamp)
FROM (
    SELECT sender as username 
    FROM messages
    WHERE sender <> '10208819391147662'
    UNION
    SELECT recipient as username 
    FROM messages
    WHERE recipient <> '10208819391147662'
) s1
LEFT JOIN messages c2 ON (s1.username = c2.sender OR s1.username = c2.recipient)
GROUP BY s1.username

I have sucessfully extracted the 1st column by using UNION. But with no lucks on the 2nd and 3rd by using MAX and Group by clause.

My final expected result should be;

username, message, timestamp

"10204456572654160","And I reply like this","1459611758570"
"10153775515332771","Yes.. What's upp..","1459611900380"
"10204464403169266","How're you doin?","1459612000666"

And here's my SQLFiddle so far. Any suggestion is appreciated.

Aucun commentaire:

Enregistrer un commentaire