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
