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/[EMAIL PROTECTED]
--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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
