Search code examples
postgresqljoincross-join

A CROSS JOIN with a RIGHT JOIN


I have a table "typl" containing only 2 values :

typl 
------
 COL
 IND

and a table "store" with 2 columns:

achl | typl 
------+------
 AAAA | IND
 AAAA | IND
 AAAA | IND
 AAAA | IND
 AAAA | IND
 AAAA | IND
 BBBB | COL
 BBBB | COL
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND

The problem : I can find a query where, for a set of achl in the store table, if there's only one typl like for achl='AAAA', all the lines will be cross joined with the "typl" table, so the result will be :

achl | typl 
------+------
 AAAA | COL
 AAAA | COL
 AAAA | COL
 AAAA | COL
 AAAA | COL
 AAAA | COL
 AAAA | IND
 AAAA | IND
 AAAA | IND
 AAAA | IND
 AAAA | IND
 AAAA | IND
 BBBB | COL
 BBBB | COL
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND
 BBBB | IND

Solution

  • SQL Fiddle

    select *
    from (
        select achl, typl
        from store
        union all
        select achl, t.typl
        from store s cross join typl t
        where (achl, t.typl) not in (
            select achl, typl
            from store
        )
    ) s
    order by achl, typl