Re: Full outer join? Cross product? How to blend two queries into single row?

On Thu, Sep 4, 2008 at 2:22 PM, Sam Mason <sam(at)samason(dot)me(dot)uk> 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