Re: Full outer join? Cross product? How to blend two queries into single row? - Mailing list pgsql-general

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