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

here table structures

table (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 result 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 show entries in a, e, , f though there no entry related them in c.

so have group data in c every possible combination of a, e, , f , show 0 if there no data available in c.

how do that?

make use of cross join, left [outer] join , coalesce:

if want cross join, pretty unusual , may produce a lot of rows, this. more aggressive after update: b , d optional now.

select a.name a_name, e.name e_name, f.name f_name       ,coalesce(sum(c.quantity), 0) sum_quantity   cross  join e cross  join f left   join b on b.a_id = a.id left   join d on d.e_id = e.id              , d.f_id = f.id left   join c on c.b_id = b.id              , c.d_id = d.id group  1,2,3; 

for lack of specification join c if both b_id and d_id have match.

additional q in comment

in last answer put parenthesis after cross join, do?

i quote the manual here:

use parentheses if necessary determine order of nesting. in absence of parentheses, joins nest left-to-right. in case join binds more tightly commas separating items.