Skip to content
Advertisement

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

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
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement