Very complex query with many joins and sums and multiplies..

[kramer65]

Hello,

I worked the past weeks on a system of which the queries got progresively complex. Now i need one which is really beyond my knowledge.

I have a DB with 5 tables like this:

styles:
style_nr|rate
-------------
style1  | 25
style2  | 30
style3  | 35
style4  | 30

orders:
order_nr|style_nr|quantity
--------------------------
order1  |style2  | 50
order2  |style2  | 30
order3  |style3  | 25

issues:
issue_id|order_nr|fabricator_id|quantity_issued
--------------------------------------------
3      |order2  | 2          | 10
4      |order2  | 2          | 10
5      |order2  | 4          | 5
6      |order3  | 3          | 15

payments:
payment_id|fabricator_id|paid
-----------------------------
4        | 1          | 100
5        | 1          | 80
6        | 3          | 130
7        | 4          | 25

fabricators:
fabricator_id|name
------------------
  1        | Piet
  2        | Heijn
  3        | kees
  4        | Jan

WHERE styles.style_nr = orders.style_nr
AND orders.order_nr = issues.order_nr
AND issues.fabricator_id = payments.fabricator_id
AND payments.fabricator_id = fabricators.fabricator_id

Now I want to make a list which looks something like this:

Fabricator|total value issues|Amount paid
-----------------------------------------
Piet    | 4000            | 3900
Heijn    | 4560            | 5000
kees    | 440              | 450
Jan      | 40              | 45

So 'total value issues' should be issues.quantity_issued * styles.rate where the rate can be taken with help from the orders table. And then i need to do that for every seperate fabricator.
And 'Amount paid' should be the sum of payments.paid for the respective fabricator.

I do know how i could get the SUM(payments.paid) for 1 fabricator. But how i would be able to get the sum of all issues.quantity_issues * styles.rate per fabricator... no idea. And how i can get both of these into 1 query...? even less ideas...

Is there maybe someone so smart that he can solve this problem? (I actually even doubt whether it is possible at all..)

[shoz]

[quote author=kramer65 link=topic=111890.msg453733#msg453733 date=1161176544]
Now I want to make a list which looks something like this:

Fabricator|total value issues|Amount paid
-----------------------------------------
Piet    | 4000            | 3900
Heijn    | 4560            | 5000
kees    | 440              | 450
Jan      | 40              | 45

So 'total value issues' should be issues.quantity_issued * styles.rate where the rate can be taken with help from the orders table. And then i need to do that for every seperate fabricator.
And 'Amount paid' should be the sum of payments.paid for the respective fabricator.

I do know how i could get the SUM(payments.paid) for 1 fabricator. But how i would be able to get the sum of all issues.quantity_issues * styles.rate per fabricator... no idea. And how i can get both of these into 1 query...? even less ideas...
[/quote]

This assumes each order has only one style_nr
[code]
SELECT
t1.*, IFNULL(SUM(p.paid), 0) AS paid
FROM
(
    SELECT
    f.fabricator_id, f.name, IFNULL(SUM(i.quantity_issued * s.rate), 0) AS issued
    FROM
    fabricators AS f
    LEFT JOIN
    issued AS i
    ON
    f.fabricator_id = i.fabricator_id
    LEFT JOIN
    orders AS o
    ON
    i.order_nr = o.order_nr
    LEFT JOIN
    styles AS s
    ON
    o.style_nr = s.style_nr
    GROUP BY
    f.fabricator_id
) AS t1
LEFT JOIN
payments AS p
ON
t1.fabricator_id = p.fabricator_id
GROUP BY
t1.fabricator_id
[/code]

[url=http://dev.mysql.com/doc/refman/4.1/en/subqueries.html]Subqueries[/url]
[url=http://dev.mysql.com/doc/refman/4.1/en/unnamed-views.html]Subqueries in FROM clause[/url]

[kramer65]

That is fantastic!! It works!

Really. This is beyond my knowledge... Although it works now i do not really understand the whole thing yet. I will start reading the links you gave me. Thanks a lot for that as well!

[fenway]

Indeed it does... shoz has a gift for composing complex queries like this.  Out of curiousity, though, doesn't SUM() automatically handle NULLs?

[shoz]

[quote=fenway]
shoz has a gift for composing complex queries like this
[/quote]

Thanks for the compliment.

[quote=fenway]
Out of curiousity, though, doesn't SUM() automatically handle NULLs?
[/quote]

It basically does. NULLs seem to be treated as 0 values if any non NULL values are part of the sum but if given only NULL values the result will be NULL. This'll be the case for a fabricator that doesn't have any payments and or doesn't have any issues.

[fenway]

Of course.... now I remember.