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.
Author |
Topic |
inbs
Aged Yak Warrior
860 Posts |
Posted - 2013-02-12 : 08:24:53
|
i have table with 3 key:agentkey, fatheragentkey, grandfatheragentkey1 0 42 1 43 1 44 0 0 and another table that hold details of agent:agentkey agent0 a1 b2 c3 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. |
|
|
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 @tab1SELECT 1, 0, 4 union allSELECT 2, 1, 4 union allSELECT 3, 1, 4 union allSELECT 4, 0, 0 --and another table that hold details of agent:DECLARE @tab2 TABLE(agentkey int, agent char(1))insert into @tab2SELECT 0, 'a' union allSELECT 1, 'b' union allSELECT 2, 'c' union allSELECT 3, 'd' union all SELECT 4, 'e'SELECT t2.agent, t3.agent, t4.agentFROM @tab1 t1 LEFT JOIN @tab2 t2 ON t2.agentkey= t1.agentkeyLEFT JOIN @tab2 t3 ON t3.agentkey= t1.fatheragentkeyLEFT JOIN @tab2 t4 ON t4.agentkey= t1.grandfatheragentkey --Chandu |
|
|
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? |
|
|
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)? |
|
|
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. |
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2013-02-12 : 14:05:00
|
thanks james ki 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 |
|
|
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 @tab1SELECT 1, 0, 4 union allSELECT 2, 1, 4 union allSELECT 3, 1, 4 union allSELECT 4, 0, 0 --and another table that hold details of agent:DECLARE @tab2 TABLE(agentkey int, agent char(1))insert into @tab2SELECT 0, 'a' union allSELECT 1, 'b' union allSELECT 2, 'c' union allSELECT 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 |
|
|
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 levelsDECLARE @tab1 TABLE(ID int,agentkey int, fatheragentkey int, grandfatheragentkey int)insert into @tab1SELECT 1, 1, 0, 4 union allSELECT 2, 2, 1, 4 union allSELECT 3, 3, 1, 4 union allSELECT 4, 4, 0, 0 --and another table that hold details of agent:DECLARE @tab2 TABLE(agentkey int, agent char(1))insert into @tab2SELECT 0, 'a' union allSELECT 1, 'b' union allSELECT 2, 'c' union allSELECT 3, 'd' union all SELECT 4, 'e'SELECT ID,[agentkey], [fatheragentkey], [grandfatheragentkey]FROM(SELECT ID,m.Cat, t2.agentFROM(SELECT ID,Cat,ValFROM @tab1UNPIVOT (Val FOR Cat IN ([agentkey], [fatheragentkey], [grandfatheragentkey]))u)mINNER JOIN @tab2 t2ON t2.agentkey = m.Val)pPIVOT(MAX(agent) FOR Cat IN ([agentkey], [fatheragentkey], [grandfatheragentkey]))q --Chandu |
|
|
|
|
|
|
|