On Sep 5, 2013, at 10:28 PM, Yuzem <[email protected]> wrote:
> Ok, wonderful, now it is working correctly but how do I select multiple
> columns from table movies?
> Should I add another sub query?
Nope. You have now changed the problem definition, so scalars will not be a
good fit. Blindly copy & paste them will not get you anywhere.
The key to success in your case is to access the movie_genre table only once,
as selectively as possible.
So, if, for a given genre you want 4 movies, you could try something along
these lines:
select genre.code,
movie.title
from (
select movie_genre.movie_id,
movie_genre.genre_id
from movie_genre
where movie_genre.genre_id = 30
order by movie_genre.id
limit 4
)
as movie_genre
join movie
on movie.id = movie_genre.movie_id
join genre
on genre.id = movie_genre.genre_id
Which gives you 4 rows:
Western|"26 Men" (1957)
Western|"A Man Called Shenandoah" (1965)
Western|"ABC Weekend Specials" (1977) {The Winged Colt (#1.5)}
Western|"Action in the Afternoon" (1953)
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
