[thelist] SQL Help Please
Joshua Olson
joshua at waetech.com
Mon May 27 18:20:01 CDT 2002
Let's pretend we have the query:
SELECT
id,
field1,
field2,
COALESCE(intfield1, intfield2, 0) AS fk_id
FROM table1
WHERE field1 = 'foo'
Now, I want to use the calculated field in a join with another table. My
SQL-92 answer would be use the following syntax:
SELECT
a.id,
a.field1,
a.field2,
a.fk_id,
table2.foreignvalue
FROM
(
SELECT
id,
field1,
field2,
COALESCE(intfield1, intfield2, 0) AS fk_id
FROM table1
WHERE field1 = 'foo'
) AS a
LEFT OUTER JOIN table2
ON table2.id = a.fk_id
Is there another way to do this without SQL-92 syntax? I realize that
whenever I'm displaying the results on the page I could just do a simple
SELECT query to grab the value from table2 during each iteration, but I want
to avoid that.
TIA,
-joshua
More information about the thelist
mailing list