CTE problem - 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

General SQL Server Forums

New to SQL Server Programming

CTE problem

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-05-30 : 06:50:21

Hi!
I am having some problem with a CTE question.

What I want is to retreive information from a personal table with the number of subordinates and the "cheif level".

This is what I have done so far:

WITH chefs_cte (ChefsNiva, Namn, AntalUnderlydande)
AS
(
	SELECT 
		Chef_id,
		(fornamn + efternamn) as Namn ,
		(SELECT COUNT(1) FROM Personal P2
		WHERE p1.chef_id = p2.personal_id
		--GROUP BY Chef_id
		) as AntalUnderlydande
	FROM Personal P1
--	GROUP BY Chef_id, Namn, AntalUnderlydande
)

SELECT ChefsNiva, Namn, AntalUnderlydande FROM chefs_cte

I can't get the number of subordinates to work!

Best Regards,
KF

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-05-30 : 07:14:53

To make a little more information, personal looks like this:
personal_id
chef_id
lonakat_id
fornamt
efternamn
tital

A little output:

4	30	1	Jonas               	Svegerud            	Säljare   
5	31	1	Petter              	Solberg             	Säljare   
6	32	1	Esbjörn             	Adamnsson           	Säljare   
7	33	1	Lorriane            	Barksdale           	Säljare   
8	34	2	Johannes            	Agerberg            	Säljare   
9	35	2	Christoffer         	Andersson           	Säljare   
10	29	2	Peter               	Svensson            	Säljare

Expected out for these records would be:

39	NULL	6	Donald              	Frump               	Chef      
17	39	2	Ronda               	Brann               	Säljare   
3	39	1	Erik                	Persson             	Säljare   
10	17	2	Peter               	Svensson            	Säljare   
24	17	3	Jens                	Backmyr             	Säljare   
25	3	3	Olof                	Håkansson           	Säljare   
11	3	2	Tobias              	Petterson           	Säljare

ChefsNiva	Namn		AntalUnderlydande
1		Donald Frump	2
2		Ronda	Brann	2
2		Erik Petersson	2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-30 : 07:50:35

Your CTE definition is wrong. To traverse a hierarchy you need both a anchor part and a recursive part.
See Books Online for more information and examples.

Also see
http://weblogs.sqlteam.com/peterl/archive/2007/10/04/Sum-up-a-tree-hierachy-in-SQL-Server-2005.aspx
http://weblogs.sqlteam.com/peterl/archive/2008/11/27/Expand-network-using-CTE-without-circular-reference.aspx

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

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-05-30 : 16:47:45

As always, thanks for your input Peso!

I have now read up a little bit more on CTE on BOL.

Now I have come this far:

WITH chefs_cte (ChefsNiva, chef_id, AntalUnderlydande)
AS
(
	-- Anchor member definition
	SELECT 
		0 as ChefsNiva,
		Chef_id,		
		COUNT(1) as AntalUnderlydande
	FROM Personal P
	GROUP BY chef_id, ChefsNiva

	UNION ALL
	
	-- Recursive member definition
	SELECT 
		ChefsNiva + 1,
		P.chef_id,
		COUNT(1) as AntalUnderlydande
	FROM Personal P
	INNER JOIN chefs_cte cte ON cte.chef_id = P.chef_id
	--GROUP BY P.chef_id, ChefsNiva
)

SELECT P.fornamn, cte.* FROM chefs_cte cte
--INNER JOIN Personal P ON P.personal_id = cte.chef_id

Now it complains about ChefsNiva in the first GROUP BY clause but at the same time I can't be without it.

Can someone give me a hint to the right direction?
Should I maybe skip the "COUNT(1) as AntalUnderlydande" and do that in a later step (maybe in the last SELECT query)?

Br,
KF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-31 : 02:54:16

You can't have a GROUP BY in the recursive part in a CTE.
The example posted here
http://weblogs.sqlteam.com/peterl/archive/2007/10/04/Sum-up-a-tree-hierachy-in-SQL-Server-2005.aspx
shows you how to SUM a hierarchy. You can very easily change this to a COUNT by putting SUM(1); ie the Amount is always 1.

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

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-06-16 : 11:11:57

Hi Peso,
sorry for the late reply.

As always, thanks for your assistance, you rock!

I do not have GROUP BY in the recursice part, it is commented.

I have now solved the case, here is the whole CTE query:


WITH chefs_cte (ChefsNiva, chef_id, personal_id)
AS
(
	-- Anchor member definition
	SELECT 
		0 as ChefsNiva,
		chef_id,
		personal_id
	FROM dbo.Personal P
	WHERE chef_id IS NULL
	

	UNION ALL
	
	-- Recursive member definition
	SELECT 
		ChefsNiva + 1,
		P.chef_id,
		P.Personal_id
	FROM dbo.Personal P
	INNER JOIN chefs_cte cte ON cte.personal_id = P.chef_id
)


SELECT 
	cte.ChefsNiva,
	(SELECT LTRIM(RTRIM(Fornamn)) + ' ' + LTRIM(RTRIM(Efternamn)) FROM dbo.Personal
	WHERE personal_id = cte.chef_id),
	COUNT(*) as AntalUnderlydande
FROM chefs_cte cte
INNER JOIN Personal P ON P.personal_id = cte.chef_id
GROUP BY cte.chef_id, cte.ChefsNiva--, P.fornamn, 
ORDER BY cte.ChefsNiva, AntalUnderlydande

Best regards,
KFluffie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources