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]
