Hi James
This is COMPLETELY untested, but here goes
select distinct a.TRAV# from docfile as a
where exists
(select * from worfile as b where
b.PROC1 = b.proc or
b.PROC2 = b.proc or
b.PROC3 = b.proc or
b.PROC4 = b.proc or
b.PROC5 = b.proc or
b.PROC6 = b.proc or
b.PROC7 = b.proc or
b.PROC8 = b.proc or
b.PROC9 = b.proc or
b.PROC10 = b.proc or
b.PROC11 = b.proc or
b.PROC12 = b.proc or
b.PROC13 = b.proc or
b.PROC14 = b.proc or
b.PROC15 = b.proc)
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
James Rich
<james@xxxxxxxxxx
m> To
Sent by: midrange-l
midrange-l-bounce <midrange-l@xxxxxxxxxxxx>
s@xxxxxxxxxxxx cc
Subject
05/25/2010 10:32 Tricky SQL statement
PM
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
Hi everyone,
I need to come up with an SQL statement that will compare a set of values
in one tables and find a matching set of rows in another table. The
relationships go like this:
A work order contains 15 fields defined as zoned 4.2 that are called
procedure numbers.
A traveler document has a many to one relationship with the traveler
procedure table. The relevant fields from this table are traveler ID
number and procedure number, defined the same as in the work order table.
Given a work order number, I need to find all traveler ID numbers whose
related traveler procedure table records match those from the work order
table, including being in the same sequence.
Or in lovely ASCII art:
__________________________________
| WO# | PROC 1 | PROC 2 | PROC 3 |
----------------------------------
| 1 | 1.01 | 1.02 | 1.03 |
----------------------------------
matches:
________________
| TRAV# | PROC |
----------------
| 123 | 1.01 |
----------------
| 123 | 1.02 |
----------------
| 123 | 1.03 |
----------------
So my SQL statement should return "123" as the traveler ID number that
matches the procedures from the given work order number.
I'm not super great at constructing SQL queries, so this might be super
simple, but I haven't been able to come up with anything that works.
Please help me out :)
James Rich
if you want to understand why that is, there are many good books on
the design of operating systems. please pass them along to redmond
when you're done reading them :)
- Paul Davis on ardour-dev
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.