I have 2 tables 'conversation' and 'participants', I would like to get the list of conversations and participants in each of them. Can I do that in only one query or I have to do 2 queries one for conversation and the second for getting participants for each conversation ?
I tried with
SELECT c.*, (SELECT p.user FROM participants p WHERE p.conversation_id = c.id ) AS participants
FROM `conversation` c
ORDER BY c.date DESC
INNER JOIN to select parts of different tables where a common ID is shared. Like this:
SELECT c.*, p.user FROM conversation AS c INNER JOIN participants AS p ON p.conversation_id = c.id ORDER BY c.date DESC