All,
I want to do something simple and the terminology is slipping me. I
want to execute two separate queries that should return 0 or 1 rows and
I want to join the results of those queries into a single row.
SELECT a.col1, a.col2
FROM mytable a
WHERE a.uid = 'abc';
SELECT b.col3, b.col4
FROM mytable b
WHERE b.uid = 'def';
But somehow get a result like this:
SELECT a.col1, a.col2, b.col3, b.col4
FROM mytable a, mytable b
WHERE a.uid = 'abc'
AND b.uid = 'def';
That query works when both a.uid and b.uid match but I want to get
results even when a.uid matches but b.uid does NOT match and vice versa.
Just make a.col1 and a.col2 NULL when a.uid does not match or make
b.col3 and b.col4 NULL when b.uid does not match. All 4 can be NULL or
no rows returned if no matches.
I came up with this query that works, but seems overly complicated:
SELECT a.col1, a.col2, b.col3, b.col4
FROM
(SELECT col1, col3, TRUE AS join_column
FROM mytable
WHERE uid = 'abc') a
FULL OUTER JOIN
(SELECT col3, col4, TRUE AS join_column
FROM mytable
WHERE uid = 'def') b
ON (a.join_column = b.join_column);
Is this how to do it, or is there a simpler syntax I'm missing? What's
the formal terminology for what I'm doing here? Is there a way to
specify a FULL OUTER JOIN without naming the join column since I don't
need one?
-- Dante
----------
D. Dante Lorenso
dante@lorenso.com
