How to select fields that are missing what you are looking for?

Question

SQL

I don't understand why I can't NOT LIKE when I want to select fields that DO NOT PRESENT the keyword.

For example (it's kind of a star wars tutorial db) if i want to select all the episodes in which Yoda is PRESENT - the following works fine.

SELECT DISTINCT Episode
FROM PERSONNAGE as P, COMPOSER as C
WHERE P.CodePers = 'yoda'
AND P.CodePers = C.CodePers

And when I want to choose, on the contrary, the episode in which Yoda is not - for some reason nothing comes out

SELECT DISTINCT Episode
FROM PERSONNAGE as P, COMPOSER as C
WHERE P.CodePers NOT LIKE 'yoda'
AND P.CodePers = C.CodePers

Where is the mistake?

Answer 1

'abc%'	Любые строки, которые начинаются с букв «abc»
'%z'	Любая последовательность символов, которая обязательно заканчивается символом «z»

WHERE P.CodePers NOT LIKE '%yoda%'

Answer 2

Error in logic. If the episode contains at least one character other than Yoda, then the condition NOT LIKE 'yoda'or != 'yoda'will be met and the episode will be selected.

Answer 3

I'm not sure about the wording, but you can try without joins with a
WHERE NOT EXISTS check, or you can solve this issue with a table difference (using except)
(Syntax errors are possible, I would play with the base myself if possible))
SELECT DISTINCT Episode
FROM PERSONNAGE as P, COMPOSER as C
WHERE NOT EXISTS
( SELECT DISTINCT Episode
FROM PERSONNAGE
WHERE CodePers = 'yoda')
AND P.CodePers = C.CodePers
SELECT DISTINCT Episode
FROM PERSONNAGE
EXCEPT
SELECT DISTINCT Episode
FROM PERSONNAGE as P, COMPOSER as C
WHERE P.CodePers = 'yoda'
AND P.CodePers = C.CodePers