Peter Brawley <[EMAIL PROTECTED]> 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
