Complex query

[kramer65]

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?

[fenway]

Try the following (UNTESTED):

[code]
SELECT orders.order_nr, orders.style_nr, orders.quantity, styles.style_nr, styles.rate, SUM(issues.quantity_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
[/code]

[kramer65]

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.