First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
======
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==========
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest.... :-p)
The field leading tells you if that actor is the leading one or not...
> > 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.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
> > 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!)
And you were right.
> > 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.)
In this example, you are using inner join... please, read the manual
about JOINs.
> > 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!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
