lundi 20 juin 2016

SQL query with joins and multiple group by

I have table messages:

enter image description here

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:

enter image description here

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