On Sep 4, 2013, at 4:21 PM, Yuzem <[email protected]> wrote:
> I want to construct genres icons and each icon must display 4 movies.
Assuming this is IMDB… what about a scalar subquery?
For example, assuming a slightly different schema from yours:
select genre.code as genre,
(
select group_concat( title )
from
(
select movie.title as title
from movie
join movie_genre
on movie_genre.movie_id = movie.id
where movie_genre.genre_id = genre.id
order by movie.title
limit 4
)
) as sample
from genre
where genre.code = 'Western'
order by genre.code
> Western|"26 Men" (1957),"A Man Called Shenandoah" (1965),"ABC Weekend
> Specials" (1977) {The Winged Colt (#1.5)},"Action in the Afternoon" (1953)
CPU Time: user 0.083246 sys 0.000443
This is for:
select count( * ) from genre;
30
select count( * ) from movie;
2545331
select count( * ) from movie_genre;
1545196
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
