How to output every combination of values in 2 columns, in groups? – SQL

I have a table where I want every parent to be mapped to every child within each group.

Input:

group_id     parent     child
1            A          E
1            B    
2            C          F
2            D
2            E          G      
3            X
3            Y

Output:

group_id     parent     child
1            A          E
1            B          E
2            C          F
2            D          F
2            E          F      
2            C          G
2            D          G
2            E          G  

So within each group, I want every value in parent to map to every value in child. If there are no values in child for a group, I want that group completely omitted (as shown where group_id = 3)

I was originally thinking of using GROUP BY 1, 2 and aggregating by something like MAX(child), but then I came across edge cases in my data where there may be >1 child. I also tried using CROSS JOIN but I’m struggling with getting my desired output. Thanks in advance.

Answer

Disclaimer, I don’t use Redshift so there may be better options. However, a CROSS JOIN should work. Just grab the DISTINCT parent values for all groups. Then do the same for the child values, and JOIN the two results together

SELECT  p.group_id, p.parent, c.child
FROM   ( 
           SELECT group_id, parent
           FROM   YourTable
           GROUP BY group_id, parent
       ) 
       p CROSS JOIN 
       (
           SELECT group_id, child
           FROM   YourTable
           WHERE  child IS NOT NULL
           GROUP BY group_id, child       
       )
       c 
WHERE  p.group_id = c.group_id
ORDER BY p.group_id, c.child, p.parent

Results:

group_id	parent	child
1	A	E
1	B	E
2	C	F
2	D	F
2	E	F
2	C	G
2	D	G
2	E	G

db<>fiddle here