List:General Discussion« Previous MessageNext Message »
From:Rhino Date:February 9 2006 11:28pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
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" <jessica@stripped>
To: <mysql@stripped>
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/mysql?unsub=1


-- 
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

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