Joining 3 tables having one-to-many and many-to-many relationships in SQL Server

Question

Thanks in advance, i am new to database design and query I have 4 tables namely film_table ,actors_table ,director_table and producer_table as follows:

film_table

(film_id, film_name, actor_id, producer_id, director_id)

actor_table (has a many to many relation with film_table)

(actor_id, actor_name)

director_table (has a many to one relation with film_table)

(director__id, director_name)

producer_table (has a many to one relation with film_table )

(producer_id, producer_name)

I also have a mapping table in the form:

Actor_Film_Mapping

(actor_id, film_id)

I need to query the database in order to obtain the data in following format:

(film_name, actor_name, producer_name, director_name)

I have tried the following Query:

SELECT f.film_name       
FROM Film_table f
INNER JOIN Actor_table a
on f.actor_id= a.actor_id
INNER JOIN Actor_Film_Mapping afm
on a.actor_id = afm.actor_id;

I have changed it in the question. In my project i have it as actor_name of type bigint. — Bharath Dinesh, Dec 07 '18 at 16:23

score 0 · Answer 1 · answered Dec 07 '18 at 16:15

0

I'm not sure why there is a Actor_id in the film table, maybe it is for the star.

Since you have the mapping table lets use it, but you may also need to include the actor from the film table if they are not also in the mapping table

SELECT f.film_name, a.actor_name       
FROM Film_table f
INNER JOIN Actor_Film_Mapping afm
on f.film_id = afm.film_id;
INNER JOIN Actor_table a
on afm.actor_id= a.actor_id

answered Dec 07 '18 at 16:15

Richard Hubley

2,180
22
29

But how do i add the producer_name and the director_name from their respective tables. – Bharath Dinesh Dec 07 '18 at 16:28
Join the tables in. but they will be repeated for ever actor if there are more than one. – Richard Hubley Dec 07 '18 at 18:35
Thank you... This surely helped – Bharath Dinesh Dec 07 '18 at 19:12

score 0 · Accepted Answer · answered Dec 07 '18 at 16:57

Any actor data in your main film_table seems to be out of place since you have (correctly) a many-to-many relationship between films and actors enforced in your mapping table. I would drop that field unless it serves some purpose that isn't relevant to the question here.

Your question seems to just require joining all of your tables together on the various ID fields. Because film-to-actor is many-to-many, your results for any given film will have the film_name, producer_name, and director_name repeated for every value of actor_name, but this query will give you all the data points that you need for your proposed answer:

SELECT 
  f.film_name,
  a.actor_name,
  p.producer_name,
  d.director_name
FROM
  film_table AS f
  JOIN
    Actor_Film_Mapping AS afm
      ON afm.actor_id = f.film_id
  JOIN
    actor_table AS a 
      ON a.actor_id = afm.actor_id
  JOIN
    producer_table AS p 
      ON p.producer_id = f.producer_id
  JOIN
    director_table AS d
      ON d.director__id = f.director__id;

I know this is not right. But is there any way of obtaining 2 actors in a single row(Without repeating the same film with different actor) — Bharath Dinesh, Dec 07 '18 at 19:24
Have a look here: https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql — Eric Brandt, Dec 07 '18 at 19:31

Joining 3 tables having one-to-many and many-to-many relationships in SQL Server

2 Answers2