Hi,
I'm just getting into MYSQL after nearly 12 years away from relational
databases and I'm trying to blow the cobwebs away. So please bear with me if
this is a simple question!
I am using a test database to teach myself MYSQL and am using my DVD
collection as the subject. I have 3 tables in this example:
Actresses
=======
actr_id
name
Actors
======
acto_id
name
DVD
======
Title
acto_id
actr_id
The acto_id and actr_id in the DVD table indicates the id for the leading
actor or actress of the movie in question. Obviously, in the case of some
movies, it may be an all-male or all-female cast, so the id fields in the
DVD table are allowed to be NULL.
I want to select the title of each movie, along with the corresponding
leading actor and/or actress name, but a straightforward join will only
return those movie titles that have NOT NULL values in BOTH the acto_id and
actr_id fields in the DVD table.
My grey-haired memory tells me that an outer join for both the actor table
and the actress table is the answer, in that the query will return all
titles *even if* one or both fields are NULL. (At least that was the case
when I was using Oracle!)
So, can somebody please correct the following query (and explain the syntax)
so that it will work please? (I haven't tried putting an outer join in it
because I don't understand the syntax.)
Select
actr.name,
acto.name,
dvd.title
from
actresses actr,
actors acto,
dvd
where
actr.actr_id = dvd.actr_id
and
acto.acto_id = dvd.acto_id
order by dvd.title;
(I used to put (+) at the end of the outer join line, but don't think this
will work in MYSQL - at least I don't see it in the manual.)
Thanks in advance for your kind help and sorry for the wordy question!
Bjorn Barton-Pye
Email: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
