List:General Discussion« Previous MessageNext Message »
From:Jessica Yazbek Date:February 9 2006 10:28pm
Subject:Multiple many-to-many SELECT
View as plain text  
Hello,

I apologize if this is a common question; I have been working with it  
and googling for days, and can't seem to find anyone who has been  
trying to do the same thing that I am. Maybe I'm using the wrong  
keywords. In any event, I am desperate for help. Here is my problem:

I have a database with several tables related on a many-to-many  
basis. Here is a simplified description:

TABLE: movies
+---------------------+---------+------+-----+---------+-------+
| Field               | Type    | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| id                  | int(11) |      | PRI | 0       |       |
| catalog_description | text    | YES  |     | NULL    |       |
| title               | text    | YES  |     | NULL    |       |
| website_url         | text    | YES  |     | NULL    |       |
+---------------------+---------+------+-----+---------+-------+

TABLE: director
+---------------------+---------+------+-----+--------- 
+----------------+
| Field               | Type    | Null | Key | Default |  
Extra          |
+---------------------+---------+------+-----+--------- 
+----------------+
| id                  | int(11) |      | UNI | NULL    |  
auto_increment |
| director_first_name | text    |      | PRI |          
|                |
| director_last_name  | text    |      | PRI |          
|                |
+---------------------+---------+------+-----+--------- 
+----------------+

TABLE: producer
+---------------------+---------+------+-----+--------- 
+----------------+
| Field               | Type    | Null | Key | Default |  
Extra          |
+---------------------+---------+------+-----+--------- 
+----------------+
| id                  | int(11) |      | UNI | NULL    |  
auto_increment |
| producer_first_name | text    |      | PRI |          
|                |
| producer_last_name  | text    |      | PRI |          
|                |
+---------------------+---------+------+-----+--------- 
+----------------+

TABLE: director_movies
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| movie_id    | int(11) |      |     | 0       |       |
| director_id | int(11) |      |     | 0       |       |
+-------------+---------+------+-----+---------+-------+

TABLE: producer_movies
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| movie_id    | int(11) |      |     | 0       |       |
| producer_id | int(11) |      |     | 0       |       |
+-------------+---------+------+-----+---------+-------+

There are actually several more related tables and fields, but I  
think this is enough to give an idea of what I have. What I am trying  
to do is SELECT all the information about a movie (all fields from  
the movies table, plus director_first_name, director_last_name,  
producer_first_name, producer_last_name) based on the id from the  
movies table. The only problem is that there may not be a producer  
and/or a director listed for a given movie, in that case, I need  
those columns to be absent from the results (ideally), or at least  
return as NULL. I was able to write a select statement that works  
exactly as I want it in the cases where there is both a director and  
a producer listed for the movie:

SELECT *
FROM movies
INNER JOIN (director INNER JOIN director_movies
	ON director_movies.director_id = director.id
	AND director_movies.movie_id=movies.id)
INNER JOIN (country INNER JOIN country_movies
	ON country_movies.country_id = country.id
	AND country_movies.movie_id=movies.id)
INNER JOIN (producer INNER JOIN producer_movies
	ON producer_movies.producer_id = producer.id
	AND producer_movies.movie_id=movies.id)
WHERE movies.id = 123

However, if there is no producer and/or director listed for the movie  
id given, then the query returns 0 rows. I thought I might need to  
change my JOINs to be LEFT JOINs insead of INNER, but when I change  
the statement so that it reads as follows:

SELECT *
FROM movies
LEFT JOIN (director INNER JOIN director_movies
	ON director_movies.director_id = director.id
	AND director_movies.movie_id=movies.id)
LEFT JOIN (country INNER JOIN country_movies
	ON country_movies.country_id = country.id
	AND country_movies.movie_id=movies.id)
LEFT JOIN (producer INNER JOIN producer_movies
	ON producer_movies.producer_id = producer.id
	AND producer_movies.movie_id=movies.id)
WHERE movies.id = 123

I get a syntax error #1064 near my WHERE clause, and I can't figure  
out what's causing it. I am new to understanding JOIN clause syntax,  
so please excuse me if it's obvious.

I'm terribly sorry for such a long post. If anyone has any clue how I  
can accomplish what I need to do, I will be so happy. As it is, I've  
been on the verge of tears over this problem for way too long!

Many thanks in advance,
Jessica Yazbek


Thread
Multiple many-to-many SELECTJessica Yazbek9 Feb
  • Re: Multiple many-to-many SELECTRhino10 Feb
    • Re: Multiple many-to-many SELECTsheeri kritzer10 Feb
      • Re: Multiple many-to-many SELECTJessica Yazbek10 Feb
Re: Multiple many-to-many SELECTJessica Yazbek12 Feb
Re: Multiple many-to-many SELECTPeter Brawley12 Feb
  • Re: Multiple many-to-many SELECTSGreen13 Feb
    • Re: Multiple many-to-many SELECTSGreen13 Feb