CODEWITHSUNDEEP
sqlpostgresql
J. Lui
J. Lui

Reputation: 105

SQL join within each group

Suppose I have Table1(A, B, C), and Table2(B)

Table1:
A    |    B    |    C
------------------------
a1        b1        c1
a1        b2        c2 
a2        b1        c3

Table2:
B   
--
b1     
b2       
b3       
b4

The result I want:

A   |   B   |   C
-------------------
a1      b1      c1
a1      b2      c2
a1      b3    [some default value]
a1      b4    [some default value]
a2      b1      c3
a2      b2    [some default value]
a2      b3    [some default value]
a2      b4    [some default value]

How can I do this?

Essentially I need a way to right outer join table1 with table2, but "group by" column A.

Upvotes: 0

Views: 63

Answers (1)

Nick
Nick

Reputation: 147266

You can achieve your desired result by CROSS JOINing the distinct values of A from Table1 with the values of B from Table2 and then LEFT JOINing to Table1 again to get the corresponding C values, using COALESCE to supply a default value where there is no matching C value:

SELECT t1.A, t2.B, COALESCE(t3.C, 'c0') AS C
FROM (SELECT DISTINCT A
      FROM Table1) t1
CROSS JOIN Table2 t2
LEFT JOIN Table1 t3 ON t3.A = t1.A AND t3.B = t2.B

Output:

a   b   c
a1  b1  c1
a1  b2  c2
a1  b3  c0
a1  b4  c0
a2  b1  c3
a2  b3  c0
a2  b2  c0
a2  b4  c0

Demo on dbfiddle

Upvotes: 3

Related Questions