> 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]
