Selecting records from one table that dont have specific relationship in a second table
Question: How can you select records from one table that doesn’t have a specific ID in a second, many-to-many table?
In other words, imagine you have a questions table (A), categories table (B), and a many-to-many relationship link table (L). This setup allows a question to have several categories. How can you find questions that don’t have a specific category (say X)?
What about the following query?
SELECT A.* FROM A JOIN L USING (question_id) WHERE L.category_id <> X;
This query will not meet the requirements because it will still return questions that have several categories in addition to the X category, since it will only filter the X value.
My way to handle this is to use the MySQL ‘NOT IN’ clause.
SELECT A.* FROM A WHERE A.question_id NOT IN (SELECT question_id FROM L WHERE category_id IN (X));
This query will retrieve all questions and related categories that don’t belong to category X.
Comments and/or different solutions are welcome.
Ilan.
If you just need the questions, and not every single category:
SELECT A.*, SUM(IF(L.category_id = X,1,0)) as count_x LEFT JOIN L USING(question_id) WHERE count_x = 0 GROUP BY question_id;
SELECT A.* FROM A
LEFT OUTER JOIN L
ON L.question_id = A.question_id
AND L.category_id = X
WHERE L.category_id IS NULL
This is a great query. Thanks
SELECT A.* FROM A
LEFT OUTER JOIN L
ON L.question_id = A.question_id
AND L.category_id = X
WHERE L.category_id IS NULL
I don’t understand that when we are taking the condition as L.category_id is NULL then Why are we taking the joining condition as L.Category_id=X
The condition
(L.question_id = A.question_id AND L.category_id = X)at the ON clause force the left join to match only the records that DO have the X category. In other words, the records without the X category will be the records with null values. All you need, in order to get only those with the null values (records that not in category X), is to restrict the query to retrieve only the records with null by putting
L.category_id IS NULLat the WHERE clause.SELECT A.* FROM A
LEFT OUTER JOIN L
ON L.question_id = A.question_id
AND L.category_id = X
WHERE L.category_id IS NULL