Dootech Dootech - 3 months ago 14
MySQL Question

Mysql : Get participants list of each conversation

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


But i get "error 1242 subquery returns more than 1 rows" and that's normal !

Answer

Use an 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