CODEWITHSUNDEEP
postgresqljoincross-join
LionelR
LionelR

Reputation: 390

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

Upvotes: 0

Views: 44

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

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

Upvotes: 1

murison
murison

Reputation: 3983

SELECT 
    * 
FROM store 
WHERE achl IN (
    SELECT achl 
    FROM store
    GROUP BY achl
    HAVING COUNT(DISTINCT typl) = 1
)

Upvotes: 1

Related Questions