sql join with nested where clause

Question

I've got a problem trying to join two tables, sample tables I have are as follows

Orders Table (Odr)

Order_No Item No  Order_Type  Req_Qty
100        A          2        45
101        B          1        32
102        F          2        23
103        A          4        23
104        C          3        14
105        B          5        43

Item Location Table (Loc)

Item_No   Location       Qty
A            X           100
A            Y           200
B            X           150
B            Y            50
C            X            75
C            Y           150
F            X           250
F            Y            60

What i want to see is, Location X's qty for each item of order types 1, 2 and 3 and for orders where Req_Qty is larger than 0 like below,

Order_No Item No  Order_Type  Req_Qty X_Qty
100        A          2        45     100
101        B          1        32     150
102        F          2        23     250
104        C          3        14     75

now ive written a query like below for this but i feel like its not giving me right results

select Odr.*, Loc.Qty
from Odr 
inner JOIN Loc
ON Odr.ITEM_no = Loc.ITEM_no
where (SOPTYPE = '1' and Req_Qty >0
 or SOPTYPE = '2' and Req_Qty >0
 or SOPTYPE = '3' and Req_Qty >0) AND Loc.Location = 'X'

could someone pleas check this for me if this is the correct way to get the result i want

thanks

@jarlh - I dont think it will make any difference in this case — Pரதீப்
– Pரதீப், Commented Jun 27, 2016 at 7:49
My guess is it's about LEFT JOIN or even outer apply and additional filters on order_type and probably something else. — IVNSTN
– IVNSTN, Commented Jun 27, 2016 at 8:07
What does but i feel like its not giving me right results mean? — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 27, 2016 at 8:19

chresse · Accepted Answer · 2016-06-27 07:54:52Z

1

the query looks fine. but you could make it more readable:

select Odr.*, Loc.Qty
from Odr 
inner JOIN Loc
ON Odr.ITEM_no = Loc.ITEM_no
where (SOPTYPE = '1' or SOPTYPE = '2' or SOPTYPE = '3') 
  and Req_Qty > 0 
  and Loc.Location = 'X'

or

select Odr.*, Loc.Qty
from Odr 
inner JOIN Loc
ON Odr.ITEM_no = Loc.ITEM_no
where SOPTYPE IS IN('1', '2', '3') 
  and Req_Qty > 0 
  and Loc.Location = 'X'

answered Jun 27, 2016 at 7:54

chresse

5,8873 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql join with nested where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related