selecting up hierarchy - 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)

selecting up hierarchy

Author

Topic

SuzyB_24
Starting Member

11 Posts

Posted - 2008-11-25 : 16:56:36

;With CTE (PageID, ImageID, ImagePath)
AS
(SELECT b.PageID, b.ImageID, b.ImagePath
 FROM tbl_Pages a LEFT JOIN tbl_Pages_Images b ON a.PageID = b.PageID
WHERE ParentID = 0
UNION ALL
SELECT b.PageID, b.ImageID, b.ImagePath
 FROM tbl_Pages a LEFT JOIN tbl_Pages_Images b ON a.PageID = b.PageID 
INNER JOIN CTE c ON c.PageID = a.ParentID
)

SELECT ImagePath from CTE

but get the error Outer join is not allowed in the recursive part of a recursive common table expression 'CTE'. I'm new to SQL Server 2005 and have never used CTE before so any help is appreciated.

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-25 : 17:37:18

Your sample data doesn't match up to your query. If you can supply proper data, I',m sure we can help. Untill then here a CTE for your first table:

DECLARE @tbl_Pages TABLE(PageID INT, ParentID INT, Name VARCHAR(50))

INSERT @tbl_Pages
SELECT 0 , null , 'home'
UNION ALL SELECT 1 , 0 , 'page a'
UNION ALL SELECT 2 , 1 , 'page a child'
UNION ALL SELECT 3 , 0 , 'page b'

DECLARE @tbl_Pages_Images TABLE (QuoteID INT, PageID INT, QuoteText VARCHAR(50))

INSERT @tbl_Pages_Images
SELECT 1 , 0 , 'quote one'
UNION ALL SELECT 2 , 3 , 'quote two'



;With CTE (PageID, ParentID, Name, PageLevel)
AS
(
	SELECT 
		a.PageID, 
		a.ParentID, 
		a.Name,
		0 AS PageLevel
	FROM 
		@tbl_Pages a 
	WHERE 
		ParentID IS NULL

UNION ALL

	SELECT 
		a.PageID, 
		a.ParentID, 
		a.Name,
		PageLevel + 1
	FROM 
		@tbl_Pages a 
	INNER JOIN
		CTE AS C
		ON a.ParentID = c.PageID
)

SELECT * 
FROM CTE

-- This will get you quotes on a page:
SELECT 
	* 
FROM 
	CTE AS C
LEFT OUTER JOIN
	@tbl_Pages_Images AS T
	ON C.PageID = T.PageId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:43:45

Lamprey, the CTE will be avialble only for first select query after definition.So second select wont work.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-26 : 00:54:19

quote:
Originally posted by visakh16

Lamprey, the CTE will be avialble only for first select query after definition.So second select wont work.

I think he was giving that as a way to actually use the rows from the CTE, not meaning that you could execute the whole thing as is. :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:57:31

quote:
Originally posted by snSQL

quote:
Originally posted by visakh16

Lamprey, the CTE will be avialble only for first select query after definition.So second select wont work.

I think he was giving that as a way to actually use the rows from the CTE, not meaning that you could execute the whole thing as is. :-)

yup...i guessed that. but was pointing it out as OP might try and feel solution wont work