Как проверить одинаковый ID у строчек SQL?

Question

[Вячеслав]

привет всем! я тут новичек и вообще в теме SQL, поэтому просьба сильно не пинать, я только начинаю грызть гранит
собственно вопрос
есть таблица вида:

user_id | thread_id
user1 | 1
user2 | 1
user3 | 2
user4 | 2
user1 | 3
user3 | 3

в функцию входят $username, $participant
я пытаюсь проверить существует ли общий thread_id у $username, $participant
и его вернуть
функция выглядит как-то так:

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();
    }

она работает, но возвращает не уникальное значение, а первое попавшееся в таблице
в чем ошибка, подскажите?

Answer 1

[Rsa97]

Вы не задали условие объединения строк, в результате получили полное декартово произведение таблицы самой на себя.

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` = ?

или

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`)

Comments

[Вячеслав]

именно этого и не хватало! Огромнейшее спасибо!
хотя я считал "условием объединения строк" строчку GROUP BY

[Rsa97]

nskarl: GROUP BY группирует строки с одинаковыми значениями указанных полей, но уже после JOIN и WHERE. То есть, в вашем случае, JOIN без ограничения генерировал все парные сочетания строк, после WHERE оставались только сочетания с указанными user_id, скажем для user1 и user3 оставались комбинации

user1 | 1 | user3 | 2
user1 | 1 | user3 | 3
user1 | 3 | user3 | 2
user1 | 3 | user3 | 3

Затем GROUP BY объединял их по последнему полю:

user1 | 1 | user3 | 2
user1 | 1 | user3 | 3

После этого SELECT DISTINCT выбирал только это поле:

2
3

Ну а уже затем первое из значений возвращалось в PHP.

[Вячеслав]

Rsa97: все верно, так и было. еще раз спасибо за подробный ответ