1

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?

forpas
  • 160,666
  • 10
  • 38
  • 76
Dorian Hawkmoon
  • 57
  • 1
  • 7
  • You're looking for that don't have the number '1' in the whole record? – lsanchezo Jun 14 '22 at 21:47
  • "In this case, looking for exercises not having tag 1, I should get Exercise 3 only." Exercise 1 has a Tag 2 therefore 1 AND 3 should be returned. What exactly are you trying to filter on? – JobesK Jun 14 '22 at 21:58

1 Answers1

1

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');
forpas
  • 160,666
  • 10
  • 38
  • 76