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!

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

Alright!!! Thanks a lot. I used it and moderated it a little bit to this: SELECT orders.order_nr, orders.style_nr, orders.quantity, orders.registered_at, styles.style_nr, styles.rate, SUM(issues.quantity_issued) AS total_issued FROM `orders` LEFT JOIN `styles` ON orders.style_nr = styles.style_nr LEFT JOIN `issues` ON orders.order_nr = issues.order_nr GROUP BY orders.order_nr Thanks.

I have to make a query for a system of a factory where i currently work. I've got 3 tables 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|quantity_issued -------------------------------------------- 3        |order2  | Pete      | 10 4        |order2  | John      | 10 5        |order2  | Leah      | 5 6        |order3  | Juan      | 15 I've made a query in which i take from the three tables the following information: order_nr|style_nr|rate|quantity. The query is as follows: <? SELECT orders.order_nr, orders.style_nr, orders.quantity, styles.style_nr, styles.rate FROM `orders`, `styles` WHERE orders.style_nr = styles.style_nr ?> However, now I also want to know for each order_nr how many i've already issued (sum of issues.quantity_issued for the respective order_nr) Maybe I have to do something with SUM() and DISTINCT(), but i don't have a clue how to combine it with the query that I have so far. How would I be able to build that within the same query. Does anybody have any idea about this?