Jump to content


Photo

Complex query


  • Please log in to reply
2 replies to this topic

#1 kramer65

kramer65
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 07 October 2006 - 01:04 PM

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?


#2 fenway

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

Posted 09 October 2006 - 05:42 AM

Try the following (UNTESTED):

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

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

#3 kramer65

kramer65
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 09 October 2006 - 12:21 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users