[GENERAL] Full outer join? Cross product? How to blend two queries into single row?
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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full outer join? Cross product? How to blend two queries into single row?
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
Re: [GENERAL] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > 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; can't you just drop the on clause altogether? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 4, 2008 at 2:22 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > This still seems a little nasty and I'd prefer to do something like: my idea seems nastiest of all: SELECT * FROM ( SELECT COUNT(*) AS rownbr, A1.col1, A1.col2 FROM Mytable AS A1 INNER JOIN Mytable AS A2 ON A1.pkey < A2.pkey AND A1.uid = A2.uid WHERE A1.uid = 'abc' GROUP BY A1.col1, A1.col2 ) AS Y( rownbr, col1, col2 ) FULL JOIN ( SELECT COUNT(*) AS rownbr, B1.col3, B1.col4 FROM Mytable AS B1 INNER JOIN Mytable AS B2 ON B1.pkey < B2.pkey AND B1.uid = B2.uid WHERE A1.uid = 'def' GROUP BY B1.col3, B1.col4 ) AS Z( rownbr, col3, col4 ) ON Y.rownbr = Z.rownbr; but I am pretty sure that this air code works. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 04, 2008 at 03:26:39PM -0600, Scott Marlowe wrote: > On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > > 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; > > can't you just drop the on clause altogether? I'm pretty sure you can't, what would be the point of a "CROSS JOIN" otherwise? This does raise the point that the SQL standard's CROSS JOIN is somewhat limited. It seems to make sense to allow the CROSS somewhere in any join type and meaning that an ON or USING isn't expected, making it valid to say: SELECT * FROM foo FULL OUTER CROSS JOIN bar; Admittedly, there aren't too many use cases for this! But it would make things a bit more regular. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > SELECT * FROM foo FULL OUTER CROSS JOIN bar; > > Admittedly, there aren't too many use cases for this! But it would make > things a bit more regular. SELECT * FROM foo, bar; Isn't this have the same results, but with out the cross join? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full outer join? Cross product? How to blend two queries into single row?
On Thu, Sep 04, 2008 at 02:58:39PM -0700, Richard Broersma wrote: > On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > > SELECT * FROM foo FULL OUTER CROSS JOIN bar; > > > > Admittedly, there aren't too many use cases for this! But it would make > > things a bit more regular. > > SELECT * FROM foo, bar; > > Isn't this have the same results, but with out the cross join? I hope not! Try running: SELECT * FROM (SELECT 1 AS a WHERE FALSE) f, (SELECT 2 AS b) b; and then try: SELECT * FROM (SELECT 1 AS a WHERE FALSE) f FULL OUTER JOIN (SELECT 2 AS b) b ON TRUE; I hope you get zero rows back from the first query, and a single row back from the second with a NULL value in the "a" column. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
