sqlsqlitewhere-clausesql-insql-except

Get all entries without a specific value in One to Many relationship SQL

I have a Exercise table, and a Tag table. Each exercise can have several tags so I have a third table for the one to many relationship.

What I try to achieve is getting all the exercises that does NOT have a specific tag.

Data example:

Exercise 1
Exercise 2
Exercise 3

Tag 1
Tag 2

Exercise 1 - Tag 1
Exercise 1 - Tag 2
Exercise 2 - Tag 1
Exercise 3 - Tag 2

In this case, looking for exercises not having tag 1, I should get Exercise 3 only.

Last attempt I have is:

SELECT Exercise.id FROM Exercise, Tags 
INNER JOIN TagsExercises 
ON Exercise.id=TagsExercises.idExercise AND TagsExercises.idTag=Tags.id 
WHERE Tags.id NOT in ( '3' )
GROUP BY Exercise.id;

And I get Exercise 3 AND Exercise 1 because of the entry with tag 2... u.u Not sure how to form the SQL, any ideas?

Solution

  • You want all the ids of the table Exercise except the ones that have a tag '1':

    SELECT id FROM Exercise
EXCEPT
SELECT idExercise FROM TagsExercises WHERE idTag = '1';

    Use the operator IN only if you want to include more than one tags.

    Or:

    SELECT id 
FROM Exercise
WHERE id NOT IN (SELECT idExercise FROM TagsExercises WHERE idTag = '1');