help needed w/difficult sql query 3

[mwa]

OK... I have a select query that I am having trouble getting straight. Please help if you can figure this out:

Table1 has the following format:
approver|fnd|fnct|obj|org|unt|proj
USERID2 |XXX|XXXX|XXX|263|XXX|8024
USERID3 |XXX|XXXX|XXX|357|XXX|8024
USERID4 |110|XXXX|XXX|XXX|XXX|8053

Table2 has the following format:
req_id|fnd|fnct|obj|org|unt|proj
123456|110|5000|511|263|400|8024
234567|110|5000|511|330|400|8053
345678|110|5000|511|263|400|8074

Table3 has the following format:
req_id|approver
123456|USERID1
234567|USERID1
345678|USERID1

What I need to do is compare table1 and table2 to determine if values (fnd,fnct,obj,org,unt,proj combined) from table2 exist in table1. In table1, XXX's represent any value (wildcard). In other words, I don't care what the value is for that field. I only care about the fields that actually hold a number. On table2, the record that has "123456|110|5000|511|263|400|8024" matches table1's record that has "USERID2 |XXX|XXXX|XXX|263|XXX|8024". Then, if a record in table2 exists in table1, then I need to update table3 and set table3.approver = table1.approver where table3.req_id = table2.req_id.

So, using the above example, table3 would be updated as follows:
req_id 123456 approver would be updated to USERID2
req_id 234567 approver would be updated to USERID4
req_id 345678 approver would not be updated

Does this make sense. Any help would be great...

mwa

 

[royjimenez]

i am not sure if i understood...

update Table3
set Table3.approver = Table1.approver
from Table1, Table2, Table3
where ...


is it ?

 

[mwa]

No, I understand that... The problem is how to determine which approver from table1 to use to update table3. The XXX's in table1 are like wildcards, so there is not a direct relationship between table1 and table2. The join would normally be:

table1 inner join table2
on 1.fnd = 1.fnd
and 1.fnct = 2.fnct
and 1.obj = 2.obj
and 1.org = 2.org
and 1.unt = 2.unt
and 1.proj = 2.proj

However this does not work because XXX does not = any of the actual values. Does that help to clear things up?

 

[SmileyConspiracy]

I think this should do what you want, but you may find it runs slowly if you’re working on lots of data.

update table3
set table3.approver = s1.approver
from table3 t3
inner join (
select req_id, approver
from table1 t1
cross join table2 t2
where t1.fnd = case when t1.fnd ='XXX' then 'XXX' else t2.fnd end
and t1.fnct = case when t1.fnct ='XXXX' then 'XXXX' else t2.fnct end
and t1.obj = case when t1.obj ='XXX' then 'XXX' else t2.obj end
and t1.org = case when t1.org ='XXX' then 'XXX' else t2.org end
and t1.unt = case when t1.unt ='XXX' then 'XXX' else t2.unt end
and t1.proj = case when t1.proj ='XXX' then 'XXX' else t2.proj end
) s1 on t3.req_id =s1.req_id

 

[MonsterBuck]

It appears by your data, that proj is required in both tables and if something in the other 5 columns matches, then you want to update the 3rd table.
So can you do something like:

table1
INNER JOIN table2
ON 1.proj = 2.proj
WHERE (1.fnd = 1.fnd
OR 1.fnct = 2.fnct
OR 1.obj = 2.obj
OR 1.org = 2.org
OR 1.unt = 2.unt)

 

[gdkz]

This should work for you.

table1 inner join table2
on (1.fnd = 2.fnd or 2.fnd = 'XXX')
and (1.fnct = 2.fnct or 2.fnct = 'XXX')
and (1.obj = 2.obj or 2.obj = 'XXX')
and (1.org = 2.org or 2.org = 'XXX')
and (1.unt = 2.unt or 2.unt = 'XXX')
and (1.proj = 2.proj or 2.proj = 'XXX')

Here if either 1.x = 2.x or 2.x = 'XXX' the and statement will be true. Thus this allows for any field to match the given arguments.

 

[mwa]

OK... furbank and gdkz, both of your queries worked...

I did a time test to see which was faster... And the winner is: gdkz.

gdkz'z query took 3-4 seconds and furbanks took 19-20 seconds.

But both queries worked with the same results. So you both get a star.

Thanks to all...
mwa