Join: three tables and a or condition

Question

I think I should know this somehow, especially after reading a lot of questions and answers regarding "The condition must go into the ON clause, not in the WHERE clause". However, I am still lost.

I have three tables, and I join them normally with LEFT (OUTER) joins. The joined tables looks like this (retty standard):


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

This is the standard query to get all the records. (It's taken from Yii; I actually want to to this with Active Record, but can't even get plain SQL right).

And now I want to get ONLY those tasks that have the question_id 2 AND 8 (e.g) So if a task has not both of those question.ids, I don't want it in the result set. In this case, the task could have other question_ids, too. Although it would be interesting to see how the query would look if it should return only those that have exactly those 2 (or any other set). It's easy to get all the tasks that have one question, with WHERE question.id = 2, but an AND in the WHERE clause leads to an empty result.

check this similar question: http://stackoverflow.com/questions/7147571/mysql-select-having-multiple-n-to-ns/7147697#7147697 — ypercubeᵀᴹ, Aug 23 '11 at 22:42

score 2 · Accepted Answer · answered Aug 23 '11 at 22:46

The WHERE clause can only apply conditions to one row at a time. But your questions of different id occur on different rows. How to solve this? Join both rows onto one row using a self-join.

Here's an example:

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

Another solution is to find the tasks that have questions 2 OR 8, and then use GROUP BY and HAVING to filter by groups that have exactly two of those.

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

Thanks, this is great. It works perfectly. I favour the second version; I could even translate it quite quickly in Yii's Active Record. — Narretz, Aug 24 '11 at 08:55
Can you please help me into this https://stackoverflow.com/questions/61745432/sql-join-three-table-with-where-clause-giving-extra-results — TheCurious, May 12 '20 at 06:44
@TheCurious, sorry, I agree with the other comments. You haven't provided enough information in that question for anyone to answer. I suspect your attempts to simplify the question has removed some crucial information about the problem. — Bill Karwin, May 12 '20 at 14:30

score 0 · Answer 2 · answered Aug 23 '11 at 20:42

0

you can do this even with out using and ... where question.id IN (2,8)

answered Aug 23 '11 at 20:42

Muhammad Usama

2,797
1
17
14

score 0 · Answer 3 · answered Aug 23 '11 at 20:43

0

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

answered Aug 23 '11 at 20:43

Adriano Carneiro

57,693
12
90
123

Thx, yeah that kind of works - but it actually returns me all the records that have at least one of these question.ids. Is it possible to get only those records that have exactly question.id 2 and question.id 8? – Narretz Aug 23 '11 at 21:27
That have **only 2 and 8**? Or have both 2 and 8 but can have others? – Adriano Carneiro Aug 23 '11 at 21:53
Good that you mention it; it came to my mind, too. In this case, they could have other question_ids, too. (although it would be interesting how it's done if it's exactly those). Important is to exclude those that don't have both of them. – Narretz Aug 23 '11 at 22:17

score 0 · Answer 4 · answered Aug 23 '11 at 20:44

This should do it

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)

score 0 · Answer 5 · answered Aug 23 '11 at 20:46

0

You're not looking for AND, you're looking for OR, or an IN:

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

Or

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

If you use AND that would mean the ID would have to be 8 and 2 at the same time. Bad deal.

answered Aug 23 '11 at 20:46

cwallenpoole

79,954
26
128
166

Thx, yeah that kind of works - but it actually returns me all the records that have at least one of these question.id. Is it possible to get only those records that have exactly question.id 2 and question.id 8? – Narretz Aug 23 '11 at 21:23

Join: three tables and a or condition

5 Answers5