Oops, I meant to send this to the list so that all could benefit.
--
Rhino
----- Original Message -----
From: "Rhino" <[EMAIL PROTECTED]>
To: "Jessica Yazbek" <[EMAIL PROTECTED]>
Sent: Sunday, February 12, 2006 4:19 PM
Subject: Re: Multiple many-to-many SELECT
Assuming you are only worried about getting the rows that match, you need
to do this:
select m.title, d.director_last_name, p.producer_last_name
from movies m inner join director_movies dm on m.id = dm.movie_id
inner join director d on dm.director_id = d.id
inner join producer_movies pm on m.id = pm.movie_id
inner join producer p on pm.producer_id = p.id;
In this case, a row will only appear in the final result set if the movie
has a corresponding row in director_movies and producer_movies and the
director_id and producer_id in those tables have matching rows in director
and producer. I don't know how familiar you are with database terminology
but these are called 'inner' joins.
If you also want to pick up rows that don't match, e.g. a movie in the
movies table has no corresponding row in the director_movies or an id in
director_movies has no corresponding row in director, then you need to do
'right' or 'left' joins. I won't attempt to explain these here but if you
need an explanation, post again and I will try to explain the concept and
give an example using your tables.
Or search the MySQL archives and you will probably find some examples and
explanations. I *think* I wrote something like that in the past year or
two since the MySQL manual is sadly deficient in describing joins.
--
Rhino
----- Original Message -----
From: "Jessica Yazbek" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, February 12, 2006 2:30 PM
Subject: Re: Multiple many-to-many SELECT
By request, here are the create statements for my tables:
movies | CREATE TABLE `movies` (
`id` int(11) NOT NULL default '0',
`catalog_description` text,
`title` text,
`website_url` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
director | CREATE TABLE `director` (
`id` int(11) NOT NULL auto_increment,
`director_first_name` text NOT NULL,
`director_last_name` text NOT NULL,
PRIMARY KEY (`director_first_name`(100),`director_last_name`(100)),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
producer | CREATE TABLE `producer` (
`id` int(11) NOT NULL auto_increment,
`producer_first_name` text NOT NULL,
`producer_last_name` text NOT NULL,
PRIMARY KEY (`producer_first_name`(100),`producer_last_name`(100)),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
director_movies | CREATE TABLE `director_movies` (
`movie_id` int(11) NOT NULL default '0',
`director_id` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
producer_movies | CREATE TABLE `producer_movies` (
`movie_id` int(11) NOT NULL default '0',
`producer_id` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Again, I'm trying to select from movies,director, and producer, using
only one SELECT statement. Director and Producer are related to movies
via the tables director_movies and producer_movies.
Thanks again!
Jessica
On Feb 12, 2006, at 11:05 AM, Peter Brawley wrote:
Jessica,
To figure out what's causing your error, I for one would need the
CREATE statements.
PB
Jessica Yazbek wrote:
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?
[EMAIL PROTECTED]
--No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.5/256 - Release Date:
2/10/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date:
2/10/2006
--
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.6/257 - Release Date:
10/02/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 10/02/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
