0

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?

William Wino
  • 3,599
  • 7
  • 38
  • 61
  • 2
    You've got a problem in that there is no correlation between A, E and F without going through D and C. And if there is no data available in C, how can you link them together? Or do you need to do a CROSS JOIN to get every possible combination of A, E and F? This would result in a huge amount of data, even if there were only 10 values in each of A, E and F you'd have 1,000 rows. – Mark Ormston Mar 25 '13 at 08:40
  • yea, I want to get every possible combination of A, E, and F. It's for reporting so it doesn't really matter if it is slow. – William Wino Mar 25 '13 at 08:46
  • It's still contradictory. If `a`, `e` and `f` are unrelated, there is a conflict when you want to join `c`. How to resolve that? – Erwin Brandstetter Mar 25 '13 at 08:50
  • Please update your question to reflect your actual requirements (that you *want* all combinations of a, e and f). – Erwin Brandstetter Mar 25 '13 at 17:51
  • @Erwin Brandstetter done – William Wino Mar 26 '13 at 05:21

1 Answers1

3

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    This won't include info from E and F when there is no matching record in C, which was one of the (impossible) requirements. – Peter Herdenborg Mar 25 '13 at 08:43
  • Huh... I never knew `GROUP BY` worked on `SELECT` index. Learn something new every day! – Mark Ormston Mar 25 '13 at 08:44
  • @MarkOrmston: You can use positional parameters in `GROUP BY` and `ORDER BY` - referring to the position in the `SELECT` list. – Erwin Brandstetter Mar 25 '13 at 08:45
  • Hmm, I think I have to cross join both E and F with B. Because if there is no entry in D, I get nothing in the result. But still I don't know how, this is so confusing haha. So cross join E and F with B and then join E and F with D? The data in D will be redundant then. – William Wino Mar 25 '13 at 10:32
  • In your last answer you put a parenthesis after cross join, what does that do? – William Wino Mar 26 '13 at 05:28
  • @William: You can instruct Postgres handle a group of joins together. Overrule the default operator precedence. You also create an optimization barrier, keeping Postgres to reorder joins as it sees fit. With plain joins this is hardly ever useful. I use them sometimes when I know from experience it's best to `JOIN` certain tables before joining to the rest. With `LEFT JOIN` / `CROSS JOIN` it may be, though. My final solution doesn't need it. I added a bit to my answer. – Erwin Brandstetter Mar 26 '13 at 05:33