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



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.