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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
