How to check the same ID for SQL strings?

Question

SQL

Hello to all! I'm new here and generally in the subject of SQL, so please do not kick much, I'm just starting to gnaw on granite .
Actually, the question
is a table of the form:
user_id | thread_id
user1 | 1
user2 | 1
user3 | 2
user4 | 2
user1 | 3
user3 | 3
the function includes $username, $participant
I'm trying to check if there is a common thread_id for $username, $participant
and to return it
, the function looks something like this:

function check_participant($username, $participant)
    {
        $sql = 'SELECT DISTINCT t.thread_id ' .
        ' FROM ' . $this->db->dbprefix . 'msg_participants p' .
        ' JOIN ' . $this->db->dbprefix . 'msg_participants t' .
        ' WHERE p.user_id = ? ' .
        ' AND t.user_id = ? ' .
        ' GROUP BY t.thread_id ';

        $query = $this->db->query($sql, array($username, $participant));

        return $query->row();
    }

it works, but returns not a unique value, but the first one found in the table
, what is the error, tell me?

Answer 1

You didn't set the condition for joining rows, as a result you got the full Cartesian product of the table with itself.

SELECT `p`.`thread_id`
    FROM `msg_participants` AS `p`
        JOIN `msg_participants` AS `t` USING(`thread_id`)
    WHERE `p`.`user_id` = ?
        AND `t`.`user_id` = ?

or

SELECT `p`.`thread_id`
    FROM (SELECT `thread_id` FROM `msg_participants` WHERE `user_id` = ?) 
        AS `p`
    JOIN (SELECT `thread_id` FROM `msg_participants` WHERE `user_id` = ?)
        AS `t` USING(`thread_id`)