self joining a table to get Parent Child relationship

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » self joining a table to get Parent Child...

12 posts, Page 1 of 2

1 2 »»

self joining a table to get Parent Child relationship

Rate Topic

Display Mode

Topic Options

Author

Message

THE-FHA

Posted Sunday, November 25, 2012 11:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

Hi,

I need some assistance with this regard. I am trying to figure out how i can Join a table to itself to get parent child relationship results, Left join works but it's giving me good reults....How can i do a CTE for that. Or some sort of solution.
Here is the table i need to self joing below:

SELECT [Dim_CostCentreN_SK]
,[Dim_CostCentreN_Code]
,[Dim_CostCentreN_Desc]
,[Dim_CostCentreN_ClientFacing]
,[Dim_CostCentreN_Level01BK]
,[Dim_CostCentreN_Level01Desc]
,[Dim_CostCentreN_Level02BK]
,[Dim_CostCentreN_Level02Desc]
,[Dim_CostCentreN_Level03BK]
,[Dim_CostCentreN_Level03Desc]
,[Dim_CostCentreN_Level04BK]
,[Dim_CostCentreN_Level04Desc]
,[Dim_CostCentreN_Level05BK]
,[Dim_CostCentreN_Level05Desc]
,[Dim_CostCentreN_Level06BK]
,[Dim_CostCentreN_Level06Desc]
,[Dim_CostCentreN_Level07BK]
,[Dim_CostCentreN_Level07Desc]
,[Dim_CostCentreN_Level08BK]
,[Dim_CostCentreN_Level08Desc]
,[Dim_CostCentreN_Level09BK]
,[Dim_CostCentreN_Level09Desc]
,[Dim_CostCentreN_Level10BK]
,[Dim_CostCentreN_Level10Desc]
,[Dim_CostCentreN_ActiveKey]
,[Dim_CostCentreN_DateStart]
,[Dim_CostCentreN_DateEnd]
FROM [BI_Blueprint_EDW].[dbo].[Dim_CostCentre_N]
GO

Post #1388440

Jason-299789

Posted Monday, November 26, 2012 1:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123

I'm not sure you can do it in a CTE, can to you provide some sample data for us to play around with.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1388459

THE-FHA

Posted Monday, November 26, 2012 1:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

SELECT TOP 1000 [Dim_CostCentre_SK]
,[Dim_CostCentre_BK]
,[Dim_CostCentre_ParentBK]
,[Dim_CostCentre_Desc]
FROM [BI_Blueprint_EDW].[dbo].[Dim_CostCentre]
----------------------------------------------------------------------------------

Dim_CostCentre_SK Dim_CostCentre_BK Dim_CostCentre_ParentBK Dim_CostCentre_Desc
1 10011070 dsst159 Actuarial & Insurance Solutions
JHB
2 10011110 dsst159 Actuarial &

Insurance
Solutions 2 JHB
3 10041070 JHsw160 Actuarial &
Insurance Solutions CTN
4 1026000 PrdL755 Regional Leader PTA
5 1026001 PrASL123 Regional Leader PTA
6 1046000 CTNd443 Regional Leader CTN

I i need to make a Parent child relationship using this table joining Costentre_ParentBK and Costcenter

Post #1388465

Jason-299789

Posted Monday, November 26, 2012 2:18 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123

Sorry I think I missunderstood, you want to generate the data from a Parent Child hierarchy, in order to populate that table, correct?

there are a few questions, as there are a couple of ways of doing this,

1) is this going to be ragged hierarchy?
2) Can data be posted against any level in the hierarchy?

the recursive CTe is probably the easiest way to implement this, the first and simplest method it to build a delimited hierarchy path with the nodes concatinated, eg Level1\Level2\Level3 then run a string splitter to split this into the sperate fields.

The Recursive Part is simple, something like this should work

WITH HierarchyBuilder_Cte
AS
(
	/*
		Root Node Anchor
	*/
	Select Dim_CostCentre_SK	
		,Dim_CostCentre_ParentBK
		,Dim_CostCentre_Desc
		,0 Level
	from Dim_CostCentre
	Where Dim_CostCentre_ParentBK IS NULL
	/*
		Get all the children 
	*/
	UNION ALL
	SELECT d.Dim_CostCentre_SK
		,d.Dim_CostCentre_ParentBK
		,p.DimcostCentre_Desc+'\'+d.Dim_CostCentre_Desc
	FROM  Dim_CostCentre d
		JOIN HierarchyBuilder_Cte p on d.Dim_CostCentre_ParentBK=p.Dim_CostCentre_SK
)
Select *
from HierarchyBuilder_Cte

There might be a problem with the join in the CTE that builds the list.

A good string splitter can be found here http://www.sqlservercentral.com/articles/Tally+Table/72993/

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1388471

THE-FHA

Posted Monday, November 26, 2012 3:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

Hi I get this error
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Post #1388489

Jason-299789

Posted Monday, November 26, 2012 3:20 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123

Sorry I forgot to add the Level into the second Select statement which should read like this

	Select Dim_CostCentre_SK	
		,Dim_CostCentre_ParentBK
		,Dim_CostCentre_Desc
		,0 Level
	from Dim_CostCentre
	Where Dim_CostCentre_ParentBK IS NULL
	/*
		Get all the children 
	*/
	UNION ALL
	SELECT d.Dim_CostCentre_SK
		,d.Dim_CostCentre_ParentBK
		,p.DimcostCentre_Desc+'\'+d.Dim_CostCentre_Desc
		,p.Level+1 Level
	FROM  Dim_CostCentre d
		JOIN HierarchyBuilder_Cte p on d.Dim_CostCentre_ParentBK=p.Dim_CostCentre_SK

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1388497

THE-FHA

Posted Monday, November 26, 2012 4:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

Hi,

Now i get
Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "Dim_CostCentre_Desc" of recursive query "HierarchyBuilder_Cte".

Post #1388536

Jason-299789

Posted Monday, November 26, 2012 5:06 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123

What Data type is the Description column, nvarchar() or varchar()?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1388543

THE-FHA

Posted Monday, November 26, 2012 5:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

varchar(255)

Post #1388549

Jason-299789

Posted Monday, November 26, 2012 5:53 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 803, Visits: 2,123

That shouldnt make a difference, but you can wrap a CAST or Convert around both levels in the union to force it into a varchar.

how wide is the column likely to get, as you might need to use a varchar(max).

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1388564

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions