Require working CTE TSQL - SQL Server Forums

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)

Require working CTE TSQL

Author

Topic

luckyguy
Starting Member

4 Posts

Posted - 2010-01-28 : 08:14:01

Hello Peers,

I'm working to come up with a CTE hierarchy query and stuck up in not getting the expected results. Here is the sample data I'm working with

Table 1

SiteId Dept Parent
======================
1 19677 0
1 19678 19677
1 19679 19677
1 19680 19677
2 9423 0
2 9424 9423
2 9425 9424
2 9426 9423
2 9427 9424
3 9813 0
3 9814 9813
3 9815 9814
3 9816 9813
3 9817 9816
4 9994 0
4 9995 9994
4 9996 9995
4 9997 9994
4 9998 9996
4 9999 9994
4 10004 9994
4 10005 10004

Table 2

Dept Parent
==============
9423 19678
9813 19679
9994 19680

Expected Result:

SiteId Dept Parent
======================
1 19677 0
1 19678 19677
1 9423 19678
1 9424 9423
1 9425 9424
1 9426 9423
1 9427 9424

1 19679 19677
1 9813 19679
1 9814 9813
1 9815 9814
1 9816 9813
1 9817 9816

1 19680 19677
1 9994 19680
1 9995 9994
1 9996 9995
1 9997 9994
1 9998 9996
1 9999 9994
1 10004 9994
1 10005 10004

I'm able to construct the CTE but when I join the intermediate table which consists of the cross reference of parent - child association of other sites, I'm getting wierd resultset back.

I would appreciate if anyone can point me to the right way.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-28 : 08:22:41

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89335
and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89365

N 56°04'39.26"
E 12°55'05.63"

luckyguy
Starting Member

4 Posts

Posted - 2010-01-28 : 11:10:57

Hello Peso,

Thanks for pointing to the links.

However, my requirement is a little bit different.

In the first table, I have all the related site level parent - child dept associations.

In the second table, I have all the cross reference parent - child dept associations for other sites.

When I select the first siteid which is equal to 1, it should return not only the one available in table 1 but also the cross-reference associations and all its sub-childs in the table 1 which are matching.

The result set shows the same.

Please let me know if I'm not clear in the example provided.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-28 : 13:45:38

Does this work for you?

WITH Foo
AS
(
	SELECT 
		T1.SiteID, 
		T1.Dept, 
		COALESCE(NULLIF(T1.Parent, 0), T2.Parent, 0) AS Parent
	FROM 
		Table1 AS T1
	LEFT OUTER JOIN
		@T2 AS T2
		ON T1.Dept = T2.Dept

	UNION ALL

	SELECT 
		T1.SiteID,
		T1.Dept,
		T1.Parent
	FROM
		Table2 AS T1
	INNER JOIN
		Foo AS F
		ON T1.SiteID = F.SiteID
		AND T1.Parent = F.Dept
)

luckyguy
Starting Member

4 Posts

Posted - 2010-01-28 : 15:17:13

Hello Lamprey,

Nope it is not returning the desired results.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-28 : 15:51:04

With the previous links, I came up with this...

-- Setup sample data
DECLARE	@Table1 TABLE
	(
		SiteID INT,
		Dept INT,
		Parent INT
	)

INSERT	@Table1
SELECT	1, 19677,     0 UNION ALL
SELECT	1, 19678, 19677 UNION ALL
SELECT	1, 19679, 19677 UNION ALL
SELECT	1, 19680, 19677 UNION ALL
SELECT	2,  9423,     0 UNION ALL
SELECT	2,  9424,  9423 UNION ALL
SELECT	2,  9425,  9424 UNION ALL
SELECT	2,  9426,  9423 UNION ALL
SELECT	2,  9427,  9424 UNION ALL
SELECT	3,  9813,     0 UNION ALL
SELECT	3,  9814,  9813 UNION ALL
SELECT	3,  9815,  9814 UNION ALL
SELECT	3,  9816,  9813 UNION ALL
SELECT	3,  9817,  9816 UNION ALL
SELECT	4,  9994,     0 UNION ALL
SELECT	4,  9995,  9994 UNION ALL
SELECT	4,  9996,  9995 UNION ALL
SELECT	4,  9997,  9994 UNION ALL
SELECT	4,  9998,  9996 UNION ALL
SELECT	4,  9999,  9994 UNION ALL
SELECT	4, 10004,  9994 UNION ALL
SELECT	4, 10005, 10004

DECLARE	@Table2 TABLE
	(
		Dept INT,
		Parent INT
	)

INSERT	@Table2
SELECT	9423, 19678 UNION ALL
SELECT	9813, 19679 UNION ALL
SELECT	9994, 19680

-- Display the result
;WITH cteSource(SiteID, Dept, Parent)
AS (
	SELECT		t1.SiteID,
			t1.Dept,
			COALESCE(t2.Parent, t1.Parent) AS Parent
	FROM		@Table1 AS t1
	LEFT JOIN	@Table2 AS t2 ON t2.Dept = t1.Dept
), cteTree(SiteID, Dept, Parent, DeptPath)
AS (
	SELECT	SiteID,
		Dept,
		Parent,
		'/' + CAST(Dept AS VARCHAR(MAX)) + '/'
	FROM	cteSource
	WHERE	Parent = 0

	UNION ALL

	SELECT		t.SiteID,
			s.Dept,
			s.Parent,
			t.DeptPath + CAST(s.Dept AS VARCHAR(MAX)) + '/'
	FROM		cteTree AS t
	INNER JOIN	cteSource AS s ON s.Parent = t.Dept
)
SELECT		SiteID,
		Dept,
		Parent
FROM		cteTree
ORDER BY	DeptPath

N 56°04'39.26"
E 12°55'05.63"

luckyguy
Starting Member

4 Posts

Posted - 2010-01-29 : 09:32:46

Hello Peso,

Thanks for leading me in the direction of the required logic to come up with the expected result set. Before posting my requirement, I did come up with the same logic but some where in the join condition I messed up which didn't work perfectly.

I've tweaked a little bit after going through certain iterations of execution and could come up with the exact results.

Once again thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources