Here ya go. I couldn't resist. It's not tested but it's close.
Format for Workfile: wko, proc1, proc2, proc3,... proc15
Format for Travel: trav, seq, proc
---With fifteen possible non-zero values in WorkFile, and possibly
having a match on all fifteen, you will have to have fifteen joins and
there is no way getting around that.
---PLUS you must exclude the ones where there are more non-zero
procedure values in one file than the other, even if the rest of them
mathc of.
---Presumably where (Travel.Seq# = 1), the value in Travel.Proc
corresponds to the Workfile.proc1, and where (Travel.Seq# = 2), the
value in Travel.Proc corresponds to the Workfile.proc2, and so on.
With XF ( trav, tproc1, tproc2, tproc3, tproc4, tproc5,
tproc6, tproc7, tproc8, tproc9, tproc11, tproc12,
tproc13, tproc14, tproc15 )
AS (Select t1.trav, t1.proc,
case when t2.proc is null then 0 else t2.proc end,
case when t3.proc is null then 0 else t3.proc end,
case when t4.proc is null then 0 else t4.proc end,
case when t5.proc is null then 0 else t5.proc end,
case when t6.proc is null then 0 else t6.proc end,
case when t7.proc is null then 0 else t7.proc end,
case when t8.proc is null then 0 else t8.proc end,
case when t9.proc is null then 0 else t9.proc end,
case when t10.proc is null then 0 else t10.proc end,
case when t11.proc is null then 0 else t11.proc end,
case when t12.proc is null then 0 else t12.proc end,
case when t13.proc is null then 0 else t13.proc end,
case when t14.proc is null then 0 else t14.proc end,
case when t15.proc is null then 0 else t15.proc end
From Travel t1
Left Outer Join Travel t2 on t2.trav = t2.trav and t2.seq = 2
Left Outer Join Travel t3 on t3.trav = t3.trav and t3.seq = 3
Left Outer Join Travel t4 on t4.trav = t4.trav and t4.seq = 5
Left Outer Join Travel t5 on t5.trav = t5.trav and t5.seq = 6
Left Outer Join Travel t6 on t6.trav = t6.trav and t6.seq = 7
Left Outer Join Travel t7 on t7.trav = t7.trav and t7.seq = 8
Left Outer Join Travel t8 on t8.trav = t8.trav and t8.seq = 9
Left Outer Join Travel t9 on t9.trav = t9.trav and t9.seq = 10
Left Outer Join Travel t10 on t10.trav = t10.trav and t10.seq = 11
Left Outer Join Travel t11 on t11.trav = t11.trav and t11.seq = 12
Left Outer Join Travel t12 on t12.trav = t12.trav and t12.seq = 12
Left Outer Join Travel t13 on t13.trav = t13.trav and t13.seq = 13
Left Outer Join Travel t14 on t14.trav = t14.trav and t14.seq = 14
Left Outer Join Travel t15 on t15.trav = t15.trav and t15.seq = 15
)
Select trav, wko
From Travel Join WorkFile
On tproc1 = proc1 and tproc2 = proc2 and tproc3 = proc3
and tproc4 = proc4 and tproc5 = proc5 and tproc6 = proc6
and tproc7 = proc7 and tproc8 = proc8 and tproc9 = proc9
and tproc10 = proc10 and tproc11 = proc11 and tproc12 = proc12
and tproc13 = proc13 and tproc14 = proc14 and tproc15 = proc15
--aec
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
midrange.com
