Re: Tricky SQL statement

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

One quick question: you say the sets (consider the procedures for a work 
order to be one set, the procedures for a traveler to be another) have 
to match "including being in the same sequence". What sequence is that, 
exactly?  There is no sequence field.  All we have is procedure number, 
unless you're considering RRN number.  If there really is only procedure 
number, then you don't have to worry about sequence, you just have to 
worry about the sets being congruent.

I know it doesn't answer your question, but I've found that any SQL 
beyond the trivial really benefits from a very thorough understanding of 
the business question.  The most successful SQL programmers I've met are 
very good at analyzing their requirement before they start messing with 
the SQL.  Those who do not create 5000 character select statements.

That being said, I'm still not sure how I'd go about solving your 
problem, although my first inclination would be to convert one file to 
match the format of the other, and then do a join.  It would take a 
little more time to try and figure out exactly how to do that, but the 
goal would be to convert the TRAV# table to a table where the fields 
would be 123, 1.01, 1.02, 1.03.  That's doable, I think.  Once you've 
got that, it's relatively simple to compare the WO# table to the newly 
reformatted TRAV# table.

Joe

> 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 |
> ----------------
>
>