SQL query problem

[kney]

I need to select movies by which preferences the user fills out.

 

By example: user 1 has filled out genre "horror", "thriller"

 

So he needs to get all movies with both those genres, if the moviegenre is "horror", or "thriller" but also if the moviegenre is "horror/thriller"

 

Now the query below only shows the movies with the single values like "horror" but not "horror/thriller" so i need to come up with some kind of LIKE statement in this query to also be able to show the other movies.

 

SELECT *
FROM MOVIE
WHERE MOVIEGENRE
IN (
SELECT GENRENAME
FROM GENRE
LEFT JOIN USERSGENRE
USING (GENREID)
WHERE USERID = 1)

[kickstart]

Hi

 

This suggests an error in how the database is designed. You probably should have another table linking genres to films.

 

Can't really see an elegant way of doing it with how you have it now.

 

If you really must then change the / to a , separating the movie genres and then use the following (and it likely will be inefficient).

 

SELECT * 
FROM MOVIE
WHERE FIND_IN_SET( (
SELECT GENRENAME
FROM GENRE
LEFT JOIN USERSGENRE
USING ( GENREID ) 
WHERE USERID =1 ) , MOVIEGENRE)

 

All the best

 

Keith

[kney]

Hmmm how would you suggest to make the database then?

[kickstart]

Hi

 

You appear to already have a table linking users and genres. Presume usersgenre will have multiple rows for each user, one for each genre they like.

 

You need a similar table linking movies to genres.

 

All the best

 

Keith

[kney]

thanks, i'll try that and let you know

[kney]

This was the final query which gave the correct result

Thanks again!

 

SELECT * 
FROM MOVIE 
WHERE MOVIEID 
IN (
    SELECT MOVIEID
    FROM MOVIEGENRE
    WHERE GENREID IN (
                                    SELECT GENREID
                                    FROM GENRE 
                                    LEFT JOIN USERSGENRE 
                                    USING (GENREID)
                                    WHERE USERID = 1))

[kickstart]

Hi

 

You could use JOINs instead:-

 

SELECT * 
FROM MOVIE 
INNER JOIN MOVIEGENRE ON MOVIE.MOVIEID = MOVIEGENRE.MOVIEID
INNER JOIN GENRE ON MOVIEGENRE.GENREID = GENRE.GENREID
INNER JOIN USERSGENRE GENRE.GENREID = USERSGENRE.GENREID
WHERE USERID = 1

 

If you do not need anything from the GENRE table then you could do without JOINing that one in.

 

You will get duplicate movies this way but a DISTINCT can solve that.

 

All the best

 

Keith