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 2012 Forums
 Transact-SQL (2012)
 join

Author  Topic 

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-02-12 : 08:24:53
i have table with 3 key:

agentkey, fatheragentkey, grandfatheragentkey
1 0 4
2 1 4
3 1 4
4 0 0


and another table that hold details of agent:

agentkey agent
0 a
1 b
2 c
3 d
4 e


i want to get a the first table and add the details of the agent (keys,names)

what is the best way to do it?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-12 : 08:33:09
I think you should join the details table 3 times but it would help if you could post the wanted result.


Too old to Rock'n'Roll too young to die.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-12 : 08:41:38
I think....

DECLARE @tab1 TABLE(agentkey int, fatheragentkey int, grandfatheragentkey int)
insert into @tab1
SELECT 1, 0, 4 union all
SELECT 2, 1, 4 union all
SELECT 3, 1, 4 union all
SELECT 4, 0, 0

--and another table that hold details of agent:
DECLARE @tab2 TABLE(agentkey int, agent char(1))
insert into @tab2
SELECT 0, 'a' union all
SELECT 1, 'b' union all
SELECT 2, 'c' union all
SELECT 3, 'd' union all
SELECT 4, 'e'

SELECT t2.agent, t3.agent, t4.agent
FROM @tab1 t1
LEFT JOIN @tab2 t2 ON t2.agentkey= t1.agentkey
LEFT JOIN @tab2 t3 ON t3.agentkey= t1.fatheragentkey
LEFT JOIN @tab2 t4 ON t4.agentkey= t1.grandfatheragentkey


--
Chandu
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-02-12 : 13:00:45
but if i have 3 or 4 ...n level , so do i need to join as the number of the level?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 13:11:38
Does the table with the agentkey, fatheragentkey and grandfatheragentkey (table @tab1 in Chandu's example) have only those 3 columns? If so it does not matter how many levels there are - you don't need to make any changes to the code.

If there are additional columns, or if the table is organized differently, you would of course need to make changes. Can you post the actual table structure with some sample data (like the sample table and data that Chandu created)?
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-12 : 14:03:14
Again...but it would help if you could post the wanted result.



Too old to Rock'n'Roll too young to die.
Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-02-12 : 14:05:00
thanks james k

i get the point, i have just 3 columns ,
i look for some more sophisticated, that if i would another level in my table(another column), so i would need to change in the code.

so thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-12 : 15:49:25
I don't know if it is any more sophisticated, it is probably less readable, but here is another way using Chandu's test data. If you have more columns then add them to the PIVOT and UNPIVOT columns.
DECLARE @tab1 TABLE(agentkey int, fatheragentkey int, grandfatheragentkey int)
insert into @tab1
SELECT 1, 0, 4 union all
SELECT 2, 1, 4 union all
SELECT 3, 1, 4 union all
SELECT 4, 0, 0

--and another table that hold details of agent:
DECLARE @tab2 TABLE(agentkey int, agent char(1))
insert into @tab2
SELECT 0, 'a' union all
SELECT 1, 'b' union all
SELECT 2, 'c' union all
SELECT 3, 'd' union all
SELECT 4, 'e'


SELECT * FROM
(
SELECT
RN,col, agent
FROM
(
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY agentkey) RN
FROM @tab1
)s
UNPIVOT (x FOR col IN (agentkey,fatheragentkey,grandfatheragentkey))U
) t1
INNER JOIN @tab2 t2 ON t1.x = t2.agentkey
) s2 PIVOT (MAX (agent) FOR col IN (agentkey,fatheragentkey,grandfatheragentkey))P
If you want to store hierarchical data such as this, there are other ways. Using hierarchyid is one way. http://technet.microsoft.com/en-us/library/bb677290.aspx
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-13 : 01:30:16
If there is PrimaryKey/UniqueKey column (like ID), then follow below approach for so many levels

DECLARE @tab1 TABLE(ID int,agentkey int, fatheragentkey int, grandfatheragentkey int)
insert into @tab1
SELECT 1, 1, 0, 4 union all
SELECT 2, 2, 1, 4 union all
SELECT 3, 3, 1, 4 union all
SELECT 4, 4, 0, 0

--and another table that hold details of agent:
DECLARE @tab2 TABLE(agentkey int, agent char(1))
insert into @tab2
SELECT 0, 'a' union all
SELECT 1, 'b' union all
SELECT 2, 'c' union all
SELECT 3, 'd' union all
SELECT 4, 'e'


SELECT ID,[agentkey], [fatheragentkey], [grandfatheragentkey]
FROM
(
SELECT ID,m.Cat, t2.agent
FROM
(
SELECT ID,Cat,Val
FROM @tab1
UNPIVOT (Val FOR Cat IN ([agentkey], [fatheragentkey], [grandfatheragentkey]))u
)m
INNER JOIN @tab2 t2
ON t2.agentkey = m.Val
)p
PIVOT(MAX(agent) FOR Cat IN ([agentkey], [fatheragentkey], [grandfatheragentkey]))q


--
Chandu
Go to Top of Page
   

- Advertisement -