On Mar 5, 2013, at 10:41 PM, Yuzem <[email protected]> wrote:
> Hello, I have the following tables:
> CREATE TABLE movies (movies,name);
> CREATE TABLE genres (movies,genres);
IMDB?
>
> Every movie has many genres and every genre has many movies.
> I want to list all genres but those who match a specified movie must be
> marked.
You might be better off normalizing your data a bit…
Say… movie, genre, movie_genre… so…
select movie.title,
genre.code
from movie
join movie_genre
on movie_genre.movie_id = movie.id
join genre
on genre.id = movie_genre.genre_id
where movie.title = 'Ted (2012)'
order by movie.title,
genre.code
title|code
Ted (2012)|Comedy
Ted (2012)|Fantasy
Ok, now, given a movie, you can get its genre.
And now you can easily show all genres and the matching movie:
select genre.code,
MovieGenre.title
from genre
left join (
select movie.title,
movie_genre.genre_id
from movie
join movie_genre
on movie_genre.movie_id = movie.id
where movie.title = 'Ted (2012)'
)
as MovieGenre
on MovieGenre.genre_id = genre.id
order by genre.code
code|title
Action|
Adult|
Adventure|
Animation|
Biography|
Comedy|Ted (2012)
Crime|
Documentary|
Drama|
Experimental|
Family|
Fantasy|Ted (2012)
Film-Noir|
Game-Show|
History|
Horror|
Lifestyle|
Music|
Musical|
Mystery|
News|
Reality-TV|
Romance|
Sci-Fi|
Short|
Sport|
Talk-Show|
Thriller|
War|
Western|
Bottom line: there are about 30 genres in IMDB, but about 1,495,677 movie →
genre mappings.
Take a look at database normalization:
http://en.wikipedia.org/wiki/Database_normalization
"Normalize until it hurts, denormalize until it works" -- anonymous
But never the other way round :)
Random Ted quote: Show us how, Flash!
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
