On Jan 4, 2012, at 12:50 PM, BareFeetWare wrote:
> Yes. This works with the above schema:
>
> select
> "cast".character_name as "Character Name"
> , "actors".name || ' ' || actors.surname as "Actor"
> , group_concat("other movies".title, ', ') as "Other movies where we've
> seen this actor"
> from "movies"
> join "cast" using (movie_id)
> join "actors" using (actor_id)
> join "cast" as "other cast" on "other cast".actor_id =
> "actors".actor_id
> join "movies" as "other movies" on "other movies".movie_id = "other
> cast".movie_id
> where "other movies".movie_id != "movies".movie_id
> and "movies".movie_id = ?
Unfortunately, looks like this query will filter out actors that have performed
in only that one movie, as the inner join to "other cast" will not match
anything. Perhaps a left outer join would be more appropriate, no?
Alternatively, relegating minor actors to obscurity might be considered a
feature :)
Here is an alternative take, using the IMdb data [1] and a marginally different
DDL [2].
select movie_cast.attribute as "Character Name",
person.name as "Actor",
(
select group_concat( other_movie.title, ', ' ) as title
from movie_cast
as other_movie_cast
join cast
as other_cast
on other_cast.id = other_movie_cast.cast_id
join movie
as other_movie
on other_movie.id = other_movie_cast.movie_id
where other_movie_cast.person_id = movie_cast.person_id
and other_movie_cast.movie_id != movie_cast.movie_id
and other_cast.code in ( 'actor', 'actress' )
) as "Other movies where we've seen this actor"
from movie_cast
join cast
on "cast".id = movie_cast.cast_id
join person
on person.id = movie_cast.person_id
where movie_cast.movie_id = 1600299
and "cast".code in ( 'actor', 'actress' )
order by person.name
Character Name|Actor|Other movies where we've seen this actor
[Woman in Restroom] <45>|Alder, Benne|appears in 3 other movies
[Judge] <19>|Beach, Scott|appears in 25 other movies
(uncredited) [T.V. Boss]|Bellan, Joe|appears in 42 other movies
[Lundy's Waiter] <39>|Bolt, Geoff|appears in 25 other movies
[Stuart's Waiter] <40>|Bright, Dick (II)|appears in 8 other movies
[Stuart 'Stu' Dunmeyer] <3>|Brosnan, Pierce|appears in 451 other movies
[Man in Men's Room] <41>|Bryant, Adam (I)|appears in 5 other movies
[Aunt Jack Hillard] <11>|Capurro, Scott|appears in 56 other movies
[Hostess Tanya] <42>|Cathcart, Tavia|appears in 1 other movies
[Ron] <29>|Cranna, James|appears in 28 other movies
[Thug] <24>|Cullen, Jim|appears in 0 other movies
[Employee] <17>|Cunningham, James (I)|appears in 56 other movies
[Bartender] <30>|Dr. Toad|appears in 0 other movies
[Miranda Hillard] <2>|Field, Sally|appears in 470 other movies
[Uncle Frank Hillard] <4>|Fierstein, Harvey|appears in 118 other movies
[Valet] <43>|Fitzsimons, C. Beau|appears in 0 other movies
[Employee] <16>|Gholson, Eva|appears in 9 other movies
[Head Chef] <34>|Guilfoyle, Paul (II)|appears in 378 other movies
[Mrs. Sellner - the Social Worker] <10>|Haney, Anne|appears in 179 other movies
(uncredited) [Waitress]|Hanson, Smadar|appears in 5 other movies
[Gloria Chaney] <5>|Holliday, Polly|appears in 188 other movies
[Lydia 'Lydie' Hillard] <6>|Jakub, Lisa|appears in 41 other movies
[Employee] <15>|Kahn, Karen (I)|appears in 17 other movies
[Christopher 'Chris' Hillard] <7>|Lawrence, Matthew (I)|appears in 286 other
movies
[Daniel's Attorney] <21>|Letchworth, Drew|appears in 14 other movies
[Alice] <23>|Lockwood, Sharon|appears in 3 other movies
[Staring Boy #2] <26>|Loo, Jeff|appears in 0 other movies
[Staring Boy #1] <25>|Loo, Kenneth|appears in 0 other movies
(uncredited) [Restaurant Guest]|Mackey, Mary (II)|appears in 21 other movies
[Miranda's Attorney] <20>|Marshall, Juliette|appears in 8 other movies
[Housekeeper] <35>|McClure, Molly|appears in 31 other movies
[A.D.R. Director Lou] <14>|McGovern, Terence|appears in 260 other movies
(uncredited) [Chris' Friend at Party]|Moegling, Lori|appears in 2 other movies
[Valet] <44>|Moeller, Jeff (I)|appears in 0 other movies
[Stunning Woman] <27>|Monroe, Betsy|appears in 24 other movies
[Justin Gregory] <13>|Mull, Martin|appears in 946 other movies
[Granny 'Warden' - Miranda's Mother] <22>|Myerson, Jessica|appears in 21 other
movies
[Delivery Boy] <28>|Narducci, Joseph|appears in 4 other movies
[Mr. Sprinkles - the Mailman] <37>|Newman, William (I)|appears in 74 other
movies
[Maitre D'] <32>|Overton, Rick|appears in 220 other movies
[Cop] <18>|Peduto, Ralph|appears in 36 other movies
[Puppeteer/Voice of 'Kovacs' the Chimp] <38>|Pray, Chris|appears in 12 other
movies
[Lundy's Secretary] <31>|Proom, Adele|appears in 9 other movies
(as Andrew L. Prosky) [T.V. Director] <36>|Prosky, Andy|appears in 7 other
movies
[Jonathan Lundy] <9>|Prosky, Robert|appears in 185 other movies
[Cook] <33>|Spencer, Dan|appears in 4 other movies
[Bus Driver] <12>|Walker, Sydney (I)|appears in 30 other movies
[Daniel Hillard/Mrs. Euphegenia Doubtfire] <1>|Williams, Robin (I)|appears in
655 other movies
[Natalie 'Nattie' Hillard] <8>|Wilson, Mara|appears in 22 other movies
[1] http://www.imdb.com/interfaces#plain
[2] http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L161
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
