Re: [sqlite] How can I improve this query?

2013-03-08 Thread James K. Lowden
On Wed, 6 Mar 2013 07:20:51 -0800 (PST) Yuzem wrote: > Given the same schema, I can list all genres with one movie by genre: > SELECT genres, movies FROM genres GROUP BY genres; HAVING count(movie) = 1 The HAVING clause does for aggregates what WHERE does for

Re: [sqlite] How can I improve this query?

2013-03-07 Thread Igor Tandetnik
On 3/6/2013 10:20 AM, Yuzem wrote: I want to get a specifed number of movies by genre, for example 3 movies by genres: Action|[movie id] Action|[movie id] Action|[movie id] Adventure|[movie id] Adventure|[movie id] Adventure|[movie id] Animation|[movie id] Animation|[movie id] Animation|[movie

Re: [sqlite] How can I improve this query?

2013-03-07 Thread Yuzem
Thank you very much, that worked perfectly, I have one more question. Given the same schema, I can list all genres with one movie by genre: SELECT genres, movies FROM genres GROUP BY genres; Action|[movie id] Adventure|[movie id] Animation|[movie id] Biography|[movie id] Comedy|[movie id]

Re: [sqlite] How can I improve this query?

2013-03-06 Thread Petite Abeille
On Mar 5, 2013, at 10:41 PM, Yuzem 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

Re: [sqlite] How can I improve this query?

2013-03-06 Thread Igor Tandetnik
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