× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




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.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.