kramer65 Posted October 18, 2006 Share Posted October 18, 2006 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 | 25style2 | 30style3 | 35style4 | 30orders:order_nr|style_nr|quantity--------------------------order1 |style2 | 50order2 |style2 | 30order3 |style3 | 25issues:issue_id|order_nr|fabricator_id|quantity_issued-------------------------------------------- 3 |order2 | 2 | 10 4 |order2 | 2 | 10 5 |order2 | 4 | 5 6 |order3 | 3 | 15payments:payment_id|fabricator_id|paid----------------------------- 4 | 1 | 100 5 | 1 | 80 6 | 3 | 130 7 | 4 | 25fabricators:fabricator_id|name------------------ 1 | Piet 2 | Heijn 3 | kees 4 | JanWHERE styles.style_nr = orders.style_nrAND orders.order_nr = issues.order_nrAND issues.fabricator_id = payments.fabricator_idAND payments.fabricator_id = fabricators.fabricator_idNow 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 | 45So '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..) Link to comment https://forums.phpfreaks.com/topic/24325-very-complex-query-with-many-joins-and-sums-and-multiplies/ Share on other sites More sharing options...
shoz Posted October 18, 2006 Share Posted October 18, 2006 [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 | 45So '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]SELECTt1.*, IFNULL(SUM(p.paid), 0) AS paidFROM( 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 t1LEFT JOINpayments AS pONt1.fabricator_id = p.fabricator_idGROUP BYt1.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] Link to comment https://forums.phpfreaks.com/topic/24325-very-complex-query-with-many-joins-and-sums-and-multiplies/#findComment-110831 Share on other sites More sharing options...
kramer65 Posted October 19, 2006 Author Share Posted October 19, 2006 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! Link to comment https://forums.phpfreaks.com/topic/24325-very-complex-query-with-many-joins-and-sums-and-multiplies/#findComment-111102 Share on other sites More sharing options...
fenway Posted October 19, 2006 Share Posted October 19, 2006 Indeed it does... shoz has a gift for composing complex queries like this. Out of curiousity, though, doesn't SUM() automatically handle NULLs? Link to comment https://forums.phpfreaks.com/topic/24325-very-complex-query-with-many-joins-and-sums-and-multiplies/#findComment-111382 Share on other sites More sharing options...
shoz Posted October 19, 2006 Share Posted October 19, 2006 [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. Link to comment https://forums.phpfreaks.com/topic/24325-very-complex-query-with-many-joins-and-sums-and-multiplies/#findComment-111439 Share on other sites More sharing options...
fenway Posted October 19, 2006 Share Posted October 19, 2006 Of course.... now I remember. Link to comment https://forums.phpfreaks.com/topic/24325-very-complex-query-with-many-joins-and-sums-and-multiplies/#findComment-111573 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.