I have table messages:
I want to retrun conversation. Conversation is when id_sender and id_recipient and id_ad are the same in rows. But for example:
First row: id_sender = 1 id_recipient = 2 id_ad = 1
Second row id_sender = 2 id_recipient = 1 id_ad = 1
this 2 rows should be group to one conversation, so I create query:
SELECT id_ad
, id_sender
, u.name
, id_recipient
, uu.name
, subject from ads.messages m
JOIN ads.advertisements
USING (id_ad)
JOIN ads.users u
ON m.id_sender = u.id_user
JOIN ads.users uu
ON m.id_recipient = uu.id_user
WHERE m.id_recipient = 133
OR m.id_sender = 133
GROUP
BY id_ad
, id_sender
, subject
, u.name
, m.id_recipient
, uu.name
that return:
This query is correct but I need return one of result on image above, query should return 3 rows
it doesn't matter which one will be return - To retrun I need all of this column and result rows shoud be 3 not 4
Any ideas?
Aucun commentaire:
Enregistrer un commentaire