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

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!

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

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

[Complex query]

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.

[Complex query]

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?