[thelist] SQL Help Please

[Joshua Olson]

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