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