Outer join question
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]
Outer join question
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]
