List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 12 2006 8:40pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
Jessica,

Your first query, with inner joins, fails with the error message

  Unknown column 'movies.id' in 'on clause

because the query inside parentheses in ...
  ...
  FROM movies
  INNER JOIN (director INNER JOIN director_movies
     ON director_movies.director_id = director.id
    AND director_movies.movie_id=movies.id)
  ...
references a table, movies, which is referenced only _outside_ the 
parenthesis. Notice too that your construct

  table1 INNER JOIN (joined_table_ref)

has not ON or USING clause, so it calls for a _cross_join_ between 
table1 and joined_table_ref! I expect you didn't intend this, but rather 
wanted ...
 
  INNER JOIN (
    director INNER JOIN director_movies ON director_movies.director_id = 
director.id
  ) ON director_movies.movie_id=movies.id
  ...

When we fix that problem, and the same problem in the other join 
clauses, we get ...

SELECT *
FROM movies
LEFT JOIN (director INNER JOIN director_movies
    ON director_movies.director_id = director.id
) ON director_movies.movie_id=movies.id
LEFT JOIN (country INNER JOIN country_movies
    ON country_movies.country_id = country.id
) ON country_movies.movie_id=movies.id
LEFT JOIN (producer INNER JOIN producer_movies
    ON producer_movies.producer_id = producer.id
) ON producer_movies.movie_id=movies.id
WHERE movies.id = 123

which throws no syntax error. Is this the query you want?

Peter Brawley
http://www.artfulsoftware,com

-----

Jessica Yazbek wrote:
> Wow, ok, I totally messed up my example.
>
> I actually have 8 related tables (+8 relating tables), so I decided to 
> try to simplify and only show two - director and producer. However, I 
> left country in the select example.
>
> So.. please also consider the following tables in my database:
>
> country | CREATE TABLE `country` (
>   `id` int(11) NOT NULL auto_increment,
>   `country_of_origin` text NOT NULL,
>   PRIMARY KEY  (`country_of_origin`(100)),
>   UNIQUE KEY `id` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
>
> | country_movies | CREATE TABLE `country_movies` (
>   `movie_id` int(11) NOT NULL default '0',
>   `country_id` int(11) NOT NULL default '0'
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> Maybe I should send this to the list...grr!
>
> Thanks for the help so far!
>
> Jessica
>
> On Feb 12, 2006, at 11:54 AM, Peter Brawley wrote:
>
>> table country_movies?
>>
>> P.
>>
>> Jessica Yazbek wrote:
>>> 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=1
>>>>>
>>>>>
>>>>>
>>>>> --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
>>>>
>>>
>>>
>>>
>>> --No virus found in this incoming message.
>>> Checked by AVG Free Edition.
>>> Version: 7.1.375 / Virus Database: 267.15.6/257 - 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
>>
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.6/257 - 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