Jessica,
It think your first mistake is that you are trying to implement many-to-many
relationships directly. Although it is theoretically possible to do so, I've
never seen anyone do it myself. I believe the normal approach is to break
each many-to-many relationship into two one-to-many relationships with an
association table (sometimes called an intersection table) in the middle.
Then, you join the tables together as needed, using inner, left or right
joins as appropriate for your situation.
I spent several hours detailing most of this in emails on this list several
months back in response to a similar question and I _really_ don't want to
go through all that typing again :-) Instead, I'm going to refer you to the
MySQL archives. If you go to this page -
http://lists.mysql.com/mysql/171636 - you will see the beginning of a thread
where I was one of the participants. We were discussing many-to-many
designs. You probably don't need to read the whole thread but certainly read
the first few posts in the thread, especially
http://lists.mysql.com/mysql/171645, which is where I first describe how to
implement a many-to-many relationship between members (of a video club) and
the titles of the movies they rent.
That should get you started. You can read more in that thread to hear more
of the pros and cons of the issue but you may find this thread wanders
somewhat and is completely relevant to your concerns.
Another thing you could try, to see other discussions of many-to-many
implementations, is to go to http://lists.mysql.com/ and fill in the search
box as follows:
Search mailing lists for: intersection table
Within: MySQL General Discussion
Matching: all of the words
since: the beginning
You may find that other people explain the idea more clearly or convincingly
than I do ;-)
I have to dash but if you have followup questions, post them in the list and
I, or someone else, will likely be able to answer.
--
Rhino
----- Original Message -----
From: "Jessica Yazbek" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, February 09, 2006 5:28 PM
Subject: Multiple many-to-many SELECT
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]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.3/254 - Release Date: 08/02/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.3/254 - Release Date: 08/02/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
