Recursive CTE with WHERE clause

Question

Post reply

Recursive CTE with WHERE clause

Shcherbunov Neil

SSCommitted

Points: 1525
More actions
August 22, 2011 at 8:30 am

#243553

Hello! I have script:
;WITH A(PersID,LastN, FID, MID)
AS
(SELECT [PersonID]
,[LastName]
,[FatherID]
,[MotherID]
FROM [Family].[dbo].[Person]
WHERE FatherID IS NULL AND MotherID IS NULL
UNION ALL
SELECT [PersonID]
,[LastName]
,[FatherID]
,[MotherID]
FROM [Family].[dbo].[Person] AS P2
WHERE P2.FatherID = A.FID
--BUT!!
--INNER JOIN A ON P2.FatherID=A.FID
--OK and no problem
)
SELECT * FROM A
and get
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "A.FID" could not be bound.
Why? I can't reference CTE itself in the WHERE clause, but in JOIN only??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 1

select 1 from dbo.tally T

where E.employee_id = 1

But!!

select 1 from dbo.tally T

join dbo.Employee E

on E.employeeid = T.num and T.num = 1

It works!!

Well, it's a bad example but I hope you get the point. In the first case, the CTE is not 'visible' to the query and hence the error. When you make a join, it is within the scope.

https://sqlroadie.com/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

If you really must use an old-style join in the recursive part of the CTE, then you can:

;WITH A(PersID,LastN, FID, MID)

AS

(SELECT [PersonID]

,[LastName]

,[FatherID]

,[MotherID]

FROM [Family].[dbo].[Person]

WHERE FatherID IS NULL AND MotherID IS NULL

UNION ALL

SELECT [PersonID]

,[LastName]

,[FatherID]

,[MotherID]

FROM [Family].[dbo].[Person] AS P2, A -- CTE referenced as old-style join.

WHERE P2.FatherID = A.FID

--BUT!!

--INNER JOIN A ON P2.FatherID=A.FID

--OK and no problem

)

SELECT * FROM A

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shcherbunov Neil SSCommitted Points: 1525 More actions · Answer 3

Arjun Sivadasan (8/22/2011)
but I hope you get the point.

Yes, I catch the idea now, thanks a lot! If we write select inside the CTE it's NOT automatically mean that outside CTE-table will be in data sources list of this select. It MUST appear in select's FROM clause, as usual. Only after that we can reference CTE in other clauses of this select. Now it's clear to me, thanks again.

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 4

No problem Neil; glad I could help.

https://sqlroadie.com/