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

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?

CodePudding user response：

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');