Arne Vanstechelman Arne Vanstechelman - 7 months ago 30
MySQL Question

MySQL - Operand should contain 1 collumn(s)

I'm currently self-studying to become a programmer and I've run into a little snag and can't seem to figure out why this particular code doesn't work...

I'm trying to create a list of movie titles that do no contain the word 'kill' or 'blood' in the title name and are of the genre thriller or horror (griezel in dutch :p).

For this I'm using the code below

SELECT filmId, titel, genre
FROM films
INNER JOIN genres
ON films.genreId = genres.genreId
WHERE titel NOT LIKE ('%blood%', '%kill%')
AND genre IN ('thriller', 'griezel')


When i try to execute this code is get an error message saying the following "Operand should contain 1 column(s)".

Any help would be much appreciated!
Thanks in advance ;)

Answer Source

You can't specify multiple patterns like that.

You'll need to use separate not like predicates and OR them:

select filmId,
    titel,
    genre
from films
inner join genres on films.genreId = genres.genreId
where (
        titel not like '%blood%'
        or titel not like '%kill%'
        )
    and genre in ('thriller', 'griezel')

Or perhaps you meant to use AND to check the title doesn't contain both blood and kill

select filmId,
    titel,
    genre
from films
inner join genres on films.genreId = genres.genreId
where (
        titel not like '%blood%'
        and titel not like '%kill%'
        )
    and genre in ('thriller', 'griezel')