Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 kramer65

kramer65
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 18 October 2006 - 01:02 PM

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..)

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 18 October 2006 - 07:11 PM

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...


This assumes each order has only one style_nr
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

Subqueries
Subqueries in FROM clause

#3 kramer65

kramer65
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 19 October 2006 - 08:06 AM

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!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 04:24 PM

Indeed it does... shoz has a gift for composing complex queries like this.  Out of curiousity, though, doesn't SUM() automatically handle NULLs?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 October 2006 - 06:03 PM

shoz has a gift for composing complex queries like this


Thanks for the compliment.

Out of curiousity, though, doesn't SUM() automatically handle NULLs?


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.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 10:06 PM

Of course.... now I remember.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users