I am not really a db expert which is without a doubt a new question.
I have a SQLite db that contains data about movies.
The primary table (movies) consists of:
- movie_id (autoincrement, primary)
- other fields
I Quickly possess the stars table:
- actor_id (autoincrement)
I Quickly possess the cast table:
- movie_id (associated with movies table)
- actor_id (associated with the stars table, eg. Robin Williams)
- character_title (the title from the character, eg. "Mrs Doubtfire")
In one query I ought to retrieve all of the figures of the given movie (in the application level I have the id from the current movie to begin), title and surname from the actor and a listing from the movies (!= this current movie) in which the actor were built with 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 possible? How?
select max(c.character_name) character_name, max(a.name) || ' ' || max(a.surname) actor, group_concat(distinct m.title) other_movies from cast c join actors a on c.actor_id = a.actor_id left join cast omc on c.actor_id = omc.actor_id and c.movie_id <> omc.movie_id left join movies m on omc.movie_id = m.movie_id where c.movie_id = ? group by a.actor_id