Re: Tricky SQL statement

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