I never had the time to THANK everyone in this thread! THANK YOU! :-)
BareFeetWare-2 wrote:
>
> On 04/01/2012, at 8:06 PM, E3 wrote:
>
>> I've a SQLite db containing data about movies.
>
> Implementing your description into a schema:
>
> create table "movies"
> ( movie_id integer primary key not null
> , title text collate nocase not null
> , year integer
> )
> ;
> create table "actors"
> ( actor_id integer primary key not null
> , name text collate nocase
> , surname text collate nocase
> )
> ;
> create table "cast"
> ( cast_id integer primary key not null
> , movie_id integer not null references "movies" (movie_id) on delete
> cascade on update cascade
> , actor_id integer not null references "actors" (actor_id) on delete
> cascade on update cascade
> , character_name text collate nocase --- the name of the character, eg.
> "Mrs Doubtfire"
> )
> ;
>
>> In a single query I should retrieve all the characters of a given movie
>> (at
>> the application level I've the id of the current movie to start), name
>> and
>> surname of the actor and a list of the movies (!= this current movie)
>> where
>> the actor had a role:
>>
>> Character_name | Actor | Other movies where we've
>> seen this actor
>> Mrs Doubtfire | Robin Williams | Mork & Mindy, Dead Poets
>> Society, ...
>> Other name | Other actor | Related movies,...
>>
>> Is this possible? How?
>
> 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 = ?
>
> Tom
>
> Tom Brodhurst-Hill
> 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
>
>
--
View this message in context:
http://old.nabble.com/retrieve-data-from-movie-sqlite-database-tp33077722p33280828.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
