如何使用左连接选择涉及当前用户的对话

The following query selects the total count of a conversation participants but list all same conversations:

$id = 4;
$query = 'SELECT COUNT(1)'
       + ' FROM tbl_conversations AS a'
       + ' LEFT JOIN tbl_conversations_participants AS b'
       + ' ON a.id = b.conversation_id';

If I add this: WHERE b.user_id = $id; it shows correct number of conversations but all same conversations are listed.

Conversations table:

Participants table:

May be you are looking for the number of participants participated in a particular conversation.

SELECT 
COUNT(DISTINCT b.user_id) totalParticipants
FROM tbl_conversations AS a
INNER JOIN tbl_conversations_participants AS b
ON a.id = b.conversation_id
WHERE a.id = 4;

If you only need the count then the following query suffices [no need to involve tbl_conversations]:

SELECT 
COUNT(DISTINCT user_id) totalParticipants
FROM tbl_conversations_participants 
WHERE conversation_id = 4;

If you want to know the total participants for each conversation:

SELECT
 a.id, 
 COUNT(DISTINCT b.user_id) totalParticipants
FROM tbl_conversations AS a
LEFT JOIN tbl_conversations_participants AS b
ON a.id = b.conversation_id
GROUP BY a.id

I don't understand.

Why you use 2 table (tbl_conversation,tbl_conversations_participants) if you want to know only count of conversation with specific user just use only tbl_conversations_participants then count distinct conversation_id

Query look like :

SELECT COUNT(DISTINCT a.conversation_id) AS count_conversation
FROM tbl_conversations_participants a
WHERE a.user_id = $id