Solved

sql query help

Posted on 2009-06-30
2
327 Views
Last Modified: 2013-12-07
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
Comment
Question by:IanPaskin
2 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 500 total points
ID: 24750030
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
 
LVL 14

Expert Comment

by:shru_0409
ID: 24752041
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now