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)
  • title
  • year
  • other fields

I Quickly possess the stars table:

  • actor_id (autoincrement)
  • title
  • surname

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