i need to have this result
order_num amount name ---------- ---------- --------------- 70007 948.5 Graham Zusi 70010 1983.43 Fabian Johns
from this tables (examples):
1) customers sort by id
id name city grade salesperson_id ------ --------------- ------------ ------ -------------- 3001 Brad Guzan London 100 5005 3002 Nick Rimando New York 100 5001 3003 Jozy Altidore Moscow 200 5007 3004 Fabian Johns Paris 300 5006 3005 Graham Zusi California 200 5002
2) orders sort by order_num
order_num amount date customer_id saleperson_id ---------- --------- ---------- ----------- ------------- 70001 150.5 2012-10-05 3005 5002 70002 65.26 2012-10-05 3002 5001 70003 2480.4 2012-10-10 3009 5003 70004 110.5 2012-08-17 3009 5003 70005 2400.6 2012-07-27 3007 5001
I need to have the result, where 500<=amount<=2000 selecting order_num , amount , name
thats my code but it doesnt work
SELECT orders.order_num, orders.amount, customers.name FROM orders, customers WHERE orders.amount >= 500 AND orders.amount <=2000 ORDER BY order_num
it gets
order_num amount name ---------- ---------- --------------- 70007 948.5 Nick Rimando 70007 948.5 Graham Zusi 70007 948.5 Brad Guzan 70007 948.5 Fabian Johns 70007 948.5 Brad Davis 70007 948.5 Geoff Cameron 70007 948.5 Julian Green 70007 948.5 Jozy Altidore 70010 1983.43 Nick Rimando 70010 1983.43 Graham Zusi 70010 1983.43 Brad Guzan 70010 1983.43 Fabian Johns 70010 1983.43 Brad Davis 70010 1983.43 Geoff Cameron 70010 1983.43 Julian Green 70010 1983.43 Jozy Altidore
Advertisement
Answer
Your query returns the cartesian product of the 2 tables because this:
FROM orders, customers
is a CROSS JOIN.
What you should do is an INNER JOIN with an ON clause so every row of orders is associated to the proper row of customers:
SELECT o.order_num, o.amount, c.name FROM orders o INNER JOIN customers c ON c.id = o.customer_id WHERE o.amount >= 500 AND o.amount <=2000 ORDER BY o.order_num