SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

inbs
Aged Yak Warrior

836 Posts

Posted - 02/12/2013 :  08:24:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/12/2013 :  08:33:09  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 02/12/2013 :  08:41:38  Show Profile  Reply with Quote
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

836 Posts

Posted - 02/12/2013 :  13:00:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 02/12/2013 :  13:11:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/12/2013 :  14:03:14  Show Profile  Visit webfred's Homepage  Reply with Quote
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

836 Posts

Posted - 02/12/2013 :  14:05:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 02/12/2013 :  15:49:25  Show Profile  Reply with Quote
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

Edited by - James K on 02/12/2013 18:00:28
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 02/13/2013 :  01:30:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000