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
