On Tuesday 19 February 2002 00:48, Todd Goldenbaum wrote: > hi, > > I'm wondering if anyone knows if it's possible to do a double-outer join in > mysql. by that I mean outer-joining on two tables instead of one (both > from the same original table). in other words, whereas a normal outer join > might go something like this: > > select b.date, u.name from billing b outer join users u on > b.user_id=u.user_id > > ...I'm trying to do something more like this: > > select b.date, u.name, l.city from billing b outer join users u on > b.user_id = u.user_id, locations l on b.location_id = l.location_id > > (that syntax is bogus) but does anyone know if this is even possible? > > thanks, > todd
As I recall you should be able to do SELECT * FROM a LEFT JOIN b ON a.x=b.x LEFT JOIN c ON c.z=b.z WHERE ... and if I understand you correctly you get what you want (which is ALL records in A even if they have no corresponding b's or c's. NOTE that the second ON might want to be a comparison between columns in a and c, not b and c depending on exactly what you need (IE in my example a missing b value would result in no returned c for that a, even if one existed). You might even need something like "ON c.z=b.z OR a.y=c.y" depending on your needs. > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php