Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql query help

Posted on 2009-06-30
2
Medium Priority
?
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 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