Trying SQL query with subquery in WHERE clause For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one.

Now, we have linked all participants to conversations. So on creating a new one, we count the number of people marked as participants (included current user (:totalUserCount)), en then would like a query to check if a conversation with x participants and all marked user(name)s exist before creating a new conversation.

After a few attempts we have a query that executes, but the result is not correct.

SELECT `app_company_user_cons`.* 
FROM (`app_company_user_cons`) 
LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_user_cons`.`id` = `link_company_user_cons_company_users`.`company_user_conversation_id` 
LEFT OUTER JOIN `app_company_users` app_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 

WHERE :totalUserCount = (SELECT 
    COUNT(*) 
    FROM (`app_company_users`) 
    LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 
    WHERE link_company_user_cons_company_users.company_user_conversation_id = app_company_user_cons.id 
) 
AND `link_company_user_cons_company_users`.`company_user_id` =  :currentUserID 
AND `app_company_users`.`name` =  :otherUserID2
AND `app_company_users`.`name` =  :otherUserID3
AND `app_company_users`.`name` =  :otherUserIDn 
LIMIT 1 


The EXPLAIN function shows me: 

+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| id | select_type        | table                                | type   | possible_keys | key     | key_len | ref                                                               | rows | Extra                                               |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 1  | PRIMARY            | NULL                                 | NULL   | NULL          | NULL    | NULL    | NULL                                                              | NULL | Impossible WHERE noticed after reading const tables |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | link_company_user_cons_company_users | ALL    | NULL          | NULL    | NULL    | NULL                                                              | 2    | Using where                                         |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | app_company_users                    | eq_ref | PRIMARY       | PRIMARY | 4       | de1210mo_CRM.link_company_user_cons_company_users.company_user_id | 1    | Using where; Using index                            |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+


Not sure if we are missing something. Any ideas on how to solve this?
Thanks in advance!

(PS: Our app adds AND 'app_company_users'.'name' = for all participants in an array)
We have a solution, no idea if it is legit ^^

SELECT 
    muc . * , 
    (
        SELECT COUNT( * )
        FROM users AS u
        LEFT OUTER JOIN link_users_conversations luc ON u.id = luc.user_id
        WHERE luc.user_conversation_id = muc.id
    ) readers
FROM user_conversations AS muc
LEFT OUTER JOIN link_users_conversations mluc ON muc.id = mluc.user_conversation_id
WHERE mluc.user_id = :currentUserID
/* Check for all 'readers' if a relation exists */
AND EXISTS (
    SELECT *
    FROM link_users_conversations AS c0
    WHERE c0.user_id = :participantID0
    AND c0.user_conversation_id = muc.id
)
AND EXISTS (
    SELECT *
    FROM link_users_conversations AS cn
    WHERE cn.user_id = :participantIDn
    AND cn.user_conversation_id = muc.id
)
HAVING readers = :totalReaders /* Total readers could be higher while all EXISTS pass. */
LIMIT 1 


I simplified names in this example.

So it mimics behaviour as seen at Facebook when starting a new Chat conversation. It will search the database if any other conversation has exactly (EXACTLY!) the same participants/readers/followers, if one exists, the new message will be posted to the existing conversation instead of creating a new one.

(FYI: The SQL provided is to check for existing conversations only. Creating messages and new conversations need other functions.)

If someone knows of a better way to do this check, please comment :)