Hey, that's exactly what I needed! And I can still use WHERE clauses to further limit my results. That's great, thank you!
I have one question though. In this LEFT JOIN syntax, you used the following format: LEFT JOIN secondary_table ON primary_table.col = secondary_table.col Is this optimized? In the case of WHERE clauses, for instance, I always put the main (most-limiting) criteria on the right side of the equals sign and the uncertain (least-limiting) criteria on the left side of the equals sign. Since I've never used LEFT JOIN before, I am unsure of the best way to do it. Erik On Friday, June 14, 2002, at 04:05 PM, Luc Foisy wrote: > How bout > > SELECT main.id, sub1.other, sub2.other FROM main LEFT JOIN sub1 ON > main.sub1fk = sub1.id LEFT JOIN sub2 ON main.sub2fk = sub2.id > > Luc > mysql,sql > >> -----Original Message----- >> From: Erik Price [mailto:[EMAIL PROTECTED]] >> Sent: Friday, June 14, 2002 3:54 PM >> To: [EMAIL PROTECTED] >> Subject: is this query possible? >> >> >> I have a query that I have in mind, but am not sure of how I can >> actually write it. It might not even be possible. I was >> hoping someone >> could tell me if I will have to use two queries instead, or >> if this will >> actually work: >> >> (In simplified form:) >> >> +--------+ >> +-------+ | main | >> | sub1 | +--------+ +-------+ >> +-------+ | id | | sub2 | >> | id |---<| sub1fk | +-------+ >> | other | | sub2fk |>---| id | >> +-------+ +--------+ | other | >> +-------+ >> >> As you can see from the simple diagram, I have a main table >> with its own >> primary key (id) but with two foreign key columns. The first one >> (sub1fk) points to the primary key of the table "sub1". The >> second one >> (sub2fk) points to the primary ky of the table "sub2". >> >> The query I'm trying to build would look something like this: >> >> SELECT main.id, >> IF(main.sub1fk,sub1.other,NULL) AS sub1other, >> IF(main.sub2fk,sub2.other,NULL) AS sub2other >> FROM main, sub1, sub2 >> WHERE main.id = some_criteria_or_other >> AND sub1.id = main.sub1fk >> AND sub2.id = main.sub2fk; >> >> >> The above SQL, of course, won't work -- because there are no >> situations >> where all of the WHERE clauses are true. Rather, I'm trying to get a >> result set that would look like this (again, this is in theory): >> >> +----+-----------+-----------+ >> | id | sub1other | sub2other | >> +----+-----------+-----------+ >> | 1 | 2 | NULL | >> | 2 | NULL | 5 | >> | 3 | NULL | 17 | >> | 4 | 8 | NULL | >> | .. | ...etc | ...etc | >> +----+-----------+-----------+ >> >> Later, in my application, I can test each column for NULL and I will >> know that the other column is the one to use (for instance, >> if the value >> of the "sub1other" column is NULL in one record, then I'll >> use the value >> of sub2other to do what I want to do, and vice versa). >> >> But this just doesn't seem possible. I can always do it with two >> separate queries if need be, but it would be elegant to do it >> with one. >> Any advice? >> >> Thanks very much, >> >> Erik >> >> >> >> ---- >> >> Erik Price >> Web Developer Temp >> Media Lab, H.H. Brown >> [EMAIL PROTECTED]> > > ---- Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] --------------------------------------------------------------------- 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
