A problem with SQL when selecting data from two tables

i need to have this result

order_num   amount      name
----------  ----------  ---------------
70007       948.5       Graham Zusi
70010       1983.43     Fabian Johns

​x
 
order_num   amount      name----------  ----------  ---------------70007       948.5       Graham Zusi70010       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

 
id      name             city          grade   salesperson_id------  ---------------  ------------  ------  --------------3001    Brad Guzan       London        100     50053002    Nick Rimando     New York      100     50013003    Jozy Altidore    Moscow        200     50073004    Fabian Johns     Paris         300     50063005    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

 
order_num   amount     date        customer_id  saleperson_id----------  ---------  ----------  -----------  -------------70001       150.5      2012-10-05  3005         500270002       65.26      2012-10-05  3002         500170003       2480.4     2012-10-10  3009         500370004       110.5      2012-08-17  3009         500370005       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

 
SELECT orders.order_num, orders.amount, customers.nameFROM orders, customersWHERE orders.amount >= 500 AND orders.amount <=2000ORDER 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

 
order_num   amount      name----------  ----------  ---------------70007       948.5       Nick Rimando70007       948.5       Graham Zusi70007       948.5       Brad Guzan70007       948.5       Fabian Johns70007       948.5       Brad Davis70007       948.5       Geoff Cameron70007       948.5       Julian Green70007       948.5       Jozy Altidore70010       1983.43     Nick Rimando70010       1983.43     Graham Zusi70010       1983.43     Brad Guzan70010       1983.43     Fabian Johns70010       1983.43     Brad Davis70010       1983.43     Geoff Cameron70010       1983.43     Julian Green70010       1983.43     Jozy Altidore​

Answer

Your query returns the cartesian product of the 2 tables because this:

FROM orders, customers

 
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

 
SELECT o.order_num, o.amount, c.nameFROM orders o INNER JOIN customers cON c.id = o.customer_idWHERE o.amount >= 500 AND o.amount <=2000ORDER BY o.order_num​

Advertisement

Answer