List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 13 2006 3:21pm
Subject:Re: Multiple many-to-many SELECT
View as plain text  
Peter Brawley <peter.brawley@stripped> wrote on 02/12/2006 03:40:52 
PM:

> 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
> >>>>>
> >>>>>

Another way to look at this is to realize that any table on the RIGHT side 
of a LEFT join is effectively LEFT joined to the query. An equivalent 
statement may be to write:

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

Now, this 
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