Posted by varkey.mathew on 12/27/06 23:24

Dear all,

I need some help from all my Transact SQL Guru friends out there..

Here is the scenario in its most simplified form.. ..

I have two tables.. A(Lookup table) and B(Transaction Table)

TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...


TableB Fields
EmployeeID
EmployeeName.......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK".... etc)

I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...

Therefore, this is what I had wrote initially..

update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null

This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...

For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match.... etc....

Therefore this is the expected match for my examples based on
requirement..

"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A


Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..


Looking forward to hearing from someone ASAP.. Please help..

Best regards,

VM...

[Back to original message]