On 3/5/2013 4:41 PM, Yuzem wrote:
Hello, I have the following tables:
CREATE TABLE movies (movies,name);
CREATE TABLE genres (movies,genres);
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.
For example for movie "tt1637725":
SELECT distinct genres,movies from genres WHERE movies='tt1637725'
UNION ALL
SELECT distinct genres,'' FROM genres WHERE genres NOT IN (SELECT distinct
genres FROM genres WHERE movies='tt1637725') group by genres order by
genres;
Try this:
select genres, max(case movies when 'tt1637725' then movies else '' end)
from genres group by genres order by genres;
An index on genres(genres) would help.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
