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.