Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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,  
0
IanPaskin
Asked:
IanPaskin
1 Solution
 
lwadwellCommented:
Hi IanPaskin,

you could try something like ...

SELECT t2.OrderRef, t2.Product, t2.Bin, t2.VALUE
FROM zones t1
JOIN orders t2
ON t2.bin between t1.binfrom and t1.binto AND t2.orderref = '123'
WHERE t1.zone = 'ONE'

lwadwell
0
 
shru_0409Commented:
select o.OrderRef,o.Product,o.Bin,o.VALUE
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now