So, what I'm trying to do is to retrieve a list of all "initial" messages a person sees in their messaging window
This is the table structure
thread_id | sender | receiver | message | date | sender_deleted | sender_received | read
xRdaQ | bTP5n | lCBNA | hello! | date | 0 | 0 |
xRdaQ | lCBNA | bTP5n | hey! | date | 0 | 0 |
1T4xR | bTP5n | An03R | hhi | date | 0 | 0 |
The queries I tried so far:
select * from messages where sender = 'bTP5n'
union select * from messages where receiver = 'bTP5n'
group by conversation_id
And I still get the two rows with the same thread_id
The same with this one query:
select * from messages where sender = 'bTP5n'
union select * from messages where receiver = 'bTP5n'
group by conversation_id order by date desc
Both of them are failing to return what I want, which is all unique thread_id where the sender or the receiver is equal to "bTP5n"
Disclaimer: Dummy data was used for this question
If you are using group by in second union query then it is only apply to the second query , if you want to apply in all the result then you have to write group by outside of all the results. Try below query:
select * from
(select * from messages where sender = 'bTP5n'
union
select * from messages where receiver = 'bTP5n'
)
as a group by conversation_id order by date desc