On Mar 5, 2013, at 10:41 PM, Yuzem <naujnit...@gmail.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users