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?
>> unsub=peter.brawley@stripped
>>
>>
>>
>> --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
>
