walking up the tree in tsql or stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

walking up the tree in tsql or stored procedure

Author

Topic

shahab03
Starting Member

28 Posts

Posted - 2008-04-03 : 14:42:17

Well I have a two tables lets say they look like as follows:

Table A
CompanyID
Location

TableB
CompanyID
CompanyName
Date

The CompanyID column has values that look like this:

AAA OR
AAA.BBB OR
AAA.BBB.CCC OR
AAA.BBB.CCC.DDD OR
AAA.BBB.CCC.DDD.EEE OR
AAA.BBB.CCC.DDD.EEE.FFF

each line representing the level of the company.

we can see that CompanyID column exists in both tables. and I would like to find out the CompanyName from table B for each companyID that exists in Table A.

but the tricky part is that for a specific CompanyID in tableA I might not have a exact match.
e.g. in A lets say I have AAA.BBB.CCC.DDD.EEE for CompanyID but in table B i might not have AAA.BBB.CCC.DDD.EEE but instead have AAA.BBB or AAA.BBB.CCC or AAA.

so I have to walk up the tree or these levels to look for a match an get the companyName. the match logic is as such.

If in table A companyID = AAA.BBB.CCC.DDD.EEE then look for same value in B if NOT FOUND then
remove the last level from the companyID value from Table A. so new value of CompanyID = AAA.BBB.CCC.DDD
Now look for this new value AAA.BBB.CCC.DDD in table B. IF NOT FOUND then

remove another level from ComapnyID in table A so new companyID = AAA.BBB.CCC and look for
AAA.BBB.CCC in table B. just going up the tree by chopping off a level till I find a match.

now the question is how to do this in TSQL.... can someone help?

So what I really want to do is to look for a match between A and B based on companyID.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-03 : 17:06:20

This seems to work base on our small sample data. if you have example that break the code then please add them to the table variable code:


declare @tA table (CompanyID varchar(200))
declare @tB table (CompanyID varchar(200), CompanyName varchar(100))

insert @tA
select 'AAA' union all
select 'AAA.BBB' union all
select 'AAA.BBB.CCC' union all
select 'AAA.BBB.CCC.DDD' union all
select 'AAA.BBB.CCC.DDD.EEE' union all
select 'AAA.BBB.CCC.DDD.EEE.FFF' 

insert @tB
select 'AAA', 'c_AAA' union all
select 'AAA.BBB.CCC', 'c_AAA.BBB.CCC' union all
select 'AAA.BBB.CCC.DDD', 'c_AAA.BBB.CCC.DDD'

select companyName
       ,[tA_CompanyID]
       ,[tB_CompanyID]
from   (
       select b.companyName
              ,a.companyid [tA_CompanyID]
              ,b.companyid [tB_CompanyID]
              ,rn = row_number() over (partition by a.companyid order by len(a.companyid) - len(b.companyid))
       from   @tB b
       join @tA a
              on charindex(reverse(b.companyid), reverse(a.companyid)) > 0
       ) d
where rn = 1

output:
companyName                 tA_CompanyID                        tB_CompanyID
------------------------------------------------------------------------------------
c_AAA                       AAA                                 AAA
c_AAA                       AAA.BBB                             AAA
c_AAA.BBB.CCC               AAA.BBB.CCC                         AAA.BBB.CCC
c_AAA.BBB.CCC.DDD           AAA.BBB.CCC.DDD                     AAA.BBB.CCC.DDD
c_AAA.BBB.CCC.DDD           AAA.BBB.CCC.DDD.EEE                 AAA.BBB.CCC.DDD
c_AAA.BBB.CCC.DDD           AAA.BBB.CCC.DDD.EEE.FFF             AAA.BBB.CCC.DDD

Be One with the Optimizer
TG

cgarcia
Starting Member

4 Posts

Posted - 2008-04-03 : 17:07:56

I think you have a problem of recursion that SQL Server can handle with CTE.

Have a look at this link: http://msdn2.microsoft.com/en-us/library/ms186243.aspx

I hope this helps,

Carlos.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources