List:General Discussion« Previous MessageNext Message »
From:Jessica Yazbek Date:February 12 2006 7:30pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
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
>

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