IanPaskin
asked on
sql query help
I have two tables to keep it simple they are ORDERS and ZONES, i have two parameters to pass to the query OrderRef and Zone, so if i have OrderRef = '123' and Zone = 'ONE' i only want results back from ORDERS table where BIN is in ZONE ONE,
ORDERS
OrderRef Product Bin VALUE
123 ABC1 A 9.99
123 ABC2 B 12.99
123 ABC3 H 15.99
123 ABC4 C 4.99
123 ABC5 S 6.99
ZONES
ZONE BINFROM BINTO
ONE A C
TWO D J
THREE K Z
Desired Result is for OrderRef ='123' and Zone='ONE' is
OrderRef Product Bin VALUE
123 ABC1 A 9.99
123 ABC2 B 12.99
123 ABC4 C 4.99
Result if OrderRef='123' and Zone='TWO' is
OrderRef Product Bin VALUE
123 ABC3 H 15.99
I could do this with two queries but would like to build it into one,
ORDERS
OrderRef Product Bin VALUE
123 ABC1 A 9.99
123 ABC2 B 12.99
123 ABC3 H 15.99
123 ABC4 C 4.99
123 ABC5 S 6.99
ZONES
ZONE BINFROM BINTO
ONE A C
TWO D J
THREE K Z
Desired Result is for OrderRef ='123' and Zone='ONE' is
OrderRef Product Bin VALUE
123 ABC1 A 9.99
123 ABC2 B 12.99
123 ABC4 C 4.99
Result if OrderRef='123' and Zone='TWO' is
OrderRef Product Bin VALUE
123 ABC3 H 15.99
I could do this with two queries but would like to build it into one,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
from orders o, ZONES z
where ascii(O.BIN) between ascii(z.BINFROM) and ascii(z.BINTO)
and z.zone = 'ONE'
this could help u