Присоединиться: три таблицы и условие или

Я думаю, что должен как-то это знать, особенно после прочтения множества вопросов и ответов о том, что «условие должно быть в предложении ON, а не в предложении WHERE». Тем не менее, я все еще потерян.

У меня есть три таблицы, и я обычно присоединяюсь к ним с помощью LEFT (OUTER) соединений. Соединяемые таблицы выглядят так (стандартно):


task_id task_questions_taskId   taskQuestions_questionId    question_id
1         1                     5                           5
1         1                     8                           8
2         2                     8                           8


SELECT `t`.`id` AS `task_id` , 
       `task_questions`.`taskId` AS `task_questions_taskId` ,
       `task_questions`.`questionId` AS `task_questions_questionId` , 
       questions.id AS question_id
FROM `task` `t`
LEFT OUTER JOIN `task_questions` `task_questions` 
    ON ( `task_questions`.`taskId` = `t`.`id` )
LEFT OUTER JOIN `question` `questions` 
    ON ( `task_questions`.`questionId` = `questions`.`id` )

Это стандартный запрос для получения всех записей. (Это взято из Yii; я действительно хочу сделать это с помощью Active Record, но не могу даже правильно понять простой SQL).

И теперь я хочу получить ТОЛЬКО те задачи, которые имеют question_id 2 И 8 (например), поэтому, если у задачи нет обоих этих question.id, я не хочу, чтобы она была в наборе результатов. В этом случае у задачи могут быть и другие question_id. Хотя было бы интересно посмотреть, как будет выглядеть запрос, если он должен возвращать только те, у которых есть именно эти 2 (или любой другой набор). Легко получить все задачи, содержащие один вопрос, с WHERE question.id = 2, но AND в предложении WHERE приводит к пустому результату.


mysql sql join sql-match-all
person Narretz    schedule 23.08.2011    source источник
comment
проверьте этот аналогичный вопрос: stackoverflow.com/questions/7147571/   -  person ypercubeᵀᴹ    schedule 24.08.2011

Ответы (5)


arrow_upward
2
arrow_downward

Предложение WHERE может одновременно применять условия только к одной строке. Но ваши вопросы с разными идентификаторами встречаются в разных строках. Как это решить? Соедините обе строки в одну, используя самосоединение.

Вот пример:

SELECT t.`id` AS `task_id`, ...
FROM `task` AS t
INNER JOIN `task_questions` AS tq2 ON ( tq2.`taskId` = t.`id` )
INNER JOIN `questions` AS q2 ON ( tq2.`questionId` = q2.`id` )
INNER JOIN `task_questions` AS tq8 ON ( tq8.`taskId` = t.`id` )
INNER JOIN `questions` AS q8 ON ( tq8.`questionId` = q8.`id` )
WHERE q2.`id` = 2 AND q8.`id` = 8

Другое решение — найти задачи, в которых есть вопросы 2 ИЛИ 8, а затем использовать GROUP BY и HAVING для фильтрации по группам, в которых есть ровно два вопроса.

SELECT t.`id` AS `task_id`, ...
FROM `task` AS t
INNER JOIN `task_questions` AS tq ON ( tq.`taskId` = t.`id` )
INNER JOIN `questions` AS q ON ( tq.`questionId` = q.`id` )
WHERE tq.`questionId` IN (2, 8)
GROUP BY t.`id`
HAVING COUNT(DISTINCT q.`id`) = 2
person Bill Karwin    schedule 23.08.2011
comment
Спасибо, это здорово. Это работает отлично. Я предпочитаю вторую версию; Я даже мог довольно быстро перевести его в Active Record Yii. - person Narretz; 24.08.2011
comment
Не могли бы вы помочь мне в этом stackoverflow.com/questions/61745432/ - person TheCurious; 12.05.2020
comment
@TheCurious, извините, я согласен с другими комментариями. Вы не предоставили достаточно информации в этом вопросе, чтобы кто-либо мог ответить. Я подозреваю, что ваши попытки упростить вопрос удалили важную информацию о проблеме. - person Bill Karwin; 12.05.2020

arrow_upward
0
arrow_downward

вы можете сделать это даже без использования and ... где question.id IN (2,8)

person Muhammad Usama    schedule 23.08.2011

arrow_upward
0
arrow_downward

Используйте 1_:

SELECT `t`.`id` AS `task_id` , 
       `task_questions`.`taskId` AS `task_questions_taskId` ,
       `task_questions`.`questionId` AS `task_questions_questionId` , 
       questions.id AS question_id
FROM `task` `t`
LEFT OUTER JOIN `task_questions` `task_questions` 
    ON ( `task_questions`.`taskId` = `t`.`id`)
LEFT OUTER JOIN `question` `questions` 
    ON ( `task_questions`.`questionId` = `questions`.`id` )
WHERE  `task_questions`.`questionId` IN (2, 8)
person Adriano Carneiro    schedule 23.08.2011
comment
Спасибо, да, это работает, но на самом деле мне возвращаются все записи, в которых есть хотя бы один из этих question.id. Можно ли получить только те записи, у которых именно question.id 2 и question.id 8? - person Narretz; 24.08.2011
comment
У которых только 2 и 8? Или есть и 2 и 8 но можно и другие? - person Adriano Carneiro; 24.08.2011
comment
Хорошо, что вы упомянули об этом; мне тоже пришло в голову. В этом случае у них могут быть и другие question_id. (хотя было бы интересно, как это делается, если это именно те). Важно исключить тех, у кого их обоих нет. - person Narretz; 24.08.2011

arrow_upward
0
arrow_downward

Это должно сделать это

SELECT `t`.`id` AS `task_id` , 
       `task_questions`.`taskId` AS `task_questions_taskId` ,
       `task_questions`.`questionId` AS `task_questions_questionId` , 
       questions.id AS question_id
FROM `task` `t`
LEFT OUTER JOIN `task_questions` `task_questions` 
    ON ( `task_questions`.`taskId` = `t`.`id` )
LEFT OUTER JOIN `question` `questions` 
    ON ( `task_questions`.`questionId` = `questions`.`id` )
WHERE  questions.id in (2,8)
person Daryl Wenman-Bateson    schedule 23.08.2011

arrow_upward
0
arrow_downward

Вы не ищете И, вы ищете ИЛИ или В:

WHERE `questions`.`id` IN (2,8) -- grab everything in the parens.

Or 

WHERE `questions`.`id` = 2 OR -- grab each item individually
      `questions`.`id` = 8

Если вы используете AND, это будет означать, что идентификатор должен быть 8 и 2 одновременно. Плохая сделка.

person cwallenpoole    schedule 23.08.2011
comment
Спасибо, да, это работает, но на самом деле он возвращает мне все записи, в которых есть хотя бы один из этих question.id. Можно ли получить только те записи, у которых именно question.id 2 и question.id 8? - person Narretz; 24.08.2011