Re: Tricky SQL statement

On 25-May-2010 21:27, James Rich wrote:

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:

__________________<ed>_
| TRAV# | PROC | Seq |
----------------------+
| 123 | 1.01 | 1 |
----------------------+
| 123 | 1.02 | 2 |
----------------------+
| 123 | 1.03 | 3 |
-----------------------

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

Another post clarified that there is a /sequence-#/ in the Traveler Procedures file [as <ed>ited above] to correlate with the respective column numbers in the Traveler WorkOrders file.... However, I am wondering how to detect when the procedures for the selected work-order-number exceed the count of procedure for a particular traveler-ID-number.? In the given example of a match, are all of the columns Proc-4 through Proc-15 the NULL value? If for example for the given work-order-number=1, if that row had included Proc-4=1.04, then I presume that row would no longer be a match for the set of TRAV#=123? Finally just to be sure, is each Proc-# column assured to correlate directly to the same #-value in the Sequence-#; e.g. can Proc-3 be NULL, such that Proc-4 then represents the Sequence#=3?

Regards, Chuck