On 05/01/2012, at 2:21 AM, Petite Abeille wrote:
> And the where clause.
>
> And perhaps add an explicit group by.
Quite right. I should have tested.
However, using a group by gets a bit inefficient since (as I understand it)
SQLite will process the entire select before applying the where clause. So it's
best to move the "other" joining into a subselect. This also takes care of the
situation where the actor might not be in any other movies. We can even do it
in a view, which we can then reuse and filter as needed:
create view "characters"
as
select
"movies".movie_id
, "cast".character_name as "Character Name"
, "actors".name || ' ' || actors.surname as "Actor"
, ( select group_concat("other movies".title, ', ')
from "cast" as "other cast"
left join "movies" as "other movies" on "other
movies".movie_id = "other cast".movie_id
where "other cast".actor_id = "actors".actor_id
and "other movies".movie_id != "movies".movie_id
) as "Other movies where we've seen this actor"
from "movies"
join "cast" using (movie_id)
join "actors" using (actor_id)
Then filter by:
select * from "characters" where movie_id = 1
I think this works as required.
Thanks,
Tom
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
