On Thu, Sep 04, 2008 at 03:43:33PM -0500, D. Dante Lorenso wrote: > 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?
The "ON" clause is just a normal expression, so you can just put a "TRUE" in there if you want a cross join. I.e. the following is a minimal full outer cross join: SELECT * FROM foo FULL OUTER JOIN bar ON TRUE; This still seems a little nasty and I'd prefer to do something like: SELECT ((SELECT ROW(a,b) FROM foo)).*, ((SELECT ROW(c,d) FROM bar)).*; And have it do the same thing (if you have more than one row returned you'd get a nice error message and everything). But I can't seem to get the syntax right, anyone got a cluebat? Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general