JOIN Multiple Tables with Many to Many Relationship

I have around 30 tables. eg: producer, director etc related to movies

each table consists of movie_id and celeb_id field

there is a table celebs which consists of fields: celeb_id celeb_name

For a specific movie_id i want all the information retrieved in a single query.

SELECT b_movies.*, bb_celebs.celeb_name 
FROM b_movies
LEFT JOIN b_producer ON b_movies.id = b_producer.movie_id 
LEFT JOIN bb_celebs ON b_producer.celeb_id = bb_celebs.celeb_id
LEFT JOIN b_director ON b_movies.id = b_director.movie_id 
LEFT JOIN bb_celebs ON b_director.celeb_id = bb_celebs.celeb_id
WHERE b_movies.id = 'UNIQUE ID';

I want the nam开发者_如何学Pythones of celebs associated with the type of work:

Director
1. ABC
2. XYZ

Producer
1. ABC2
2. XYZ2

All names of celebs are stored in celebs table with celeb_id and celeb_name and in producer table movie_id and celeb_id and same structure applies to directors, etc.. all other tables

Please help.

Regards,

Kenh

SELECT ...
FROM ...
LEFT JOIN ... ON ...
WHERE ...
ORDER BY ...

Appropriate values for ... are left as an excercise to the OP...

(select 'Director', celeb.name from celebs c join director d on c.celeb_id = d.celeb_id and d.movie_id = "movie_id")

union all

(select 'Producer', celeb.name from celebs c join producer p on c.celeb_id = p.celeb_id and p.movie_id = "movie_id")

union all .....