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

Question

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

У меня есть три таблицы, и я обычно присоединяюсь к ним с ЛЕВЫМИ (ВНЕШНИМИ) объединениями. Объединенные таблицы выглядят так (стандарт Retty):

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.ids, я не хочу, чтобы она была в наборе результатов. В этом случае у задачи могут быть и другие question_ids. Хотя было бы интересно посмотреть, как будет выглядеть запрос, если он будет возвращать только те, которые имеют именно эти 2 (или любой другой набор). Легко получить все задачи, которые имеют один вопрос, с WHERE question.id = 2, но AND в предложении WHERE приводит к пустому результату.

Answer 1

Предложение 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

Answer 2

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

Answer 3

Использование IN:

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)

Answer 4

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

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

Или же

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

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

Answer 5

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

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)