How to group a set of row from a table by every possible combination of 3 other tables?

Question

Here are the table structures

Table A (id, name)
Table B (id, A-id)
Table C (id, quantity, B-id, D-id)
Table D (id, E-id, F-id)
Table E (id, name)
Table F (id, name)

I want to get the result like this

A.name | E.name | F.name | SUM(C.quantity) 
-------------------------------------------
foo    | aaa    | zzz    | 50
-------------------------------------------
foo    | aaa    | xxx    | 0
-------------------------------------------
foo    | bbb    | www    | 10
-------------------------------------------
bar    | aaa    | zzz    | 12
-------------------------------------------
bar    | aaa    | xxx    | 1
-------------------------------------------
bar    | bbb    | www    | 30
-------------------------------------------

I have to show all entries in A, E, and F even though there is no entry related to them in C.

So basically I have to group all data in C by every possible combination of A, E, and F and show 0 if there is no data available in C.

How do I do that?

Answer 1

Make use of CROSS JOIN, LEFT [OUTER] JOIN and COALESCE:

If you want a CROSS JOIN, which is pretty unusual and may produce a lot of rows, it could look like this. Even more aggressive after update: b and d are also optional now.

SELECT a.name AS a_name, e.name AS e_name, f.name AS f_name
      ,COALESCE(sum(c.quantity), 0) As sum_quantity
FROM   a
CROSS  JOIN e
CROSS  JOIN f
LEFT   JOIN b ON b.a_id = a.id
LEFT   JOIN d ON d.e_id = e.id
             AND d.f_id = f.id
LEFT   JOIN c ON c.b_id = b.id
             AND c.d_id = d.id
GROUP  BY 1,2,3;

For lack of specification I join c only if both b_id and d_id have a match.

Additional Q in comment

In your last answer you put a parenthesis after cross join, what does that do?

I quote the manual here:

Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM items.