How to solve this error, The multipart identifier "column" could not be bound?-CodePudding

I'm trying out solution of SQL Query for Parent Child Relationship with the queries:

with [CTE] as (
    select [ParentId]
    , [NodeId]
    from [TheTable] c where c.[ParentId] = 1
    union all
    select [ParentId]
    , [NodeId]
    from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]
)
select * from [CTE]

the errors:

The multipart identifier "p.[NodeId]" could not be bound

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Not sure what to do next.

I expect it to return child at all level of a parent category.

e.g. for nodeId = 1, it should return 3, 4, 5, 6.

CodePudding user response：

You have [ParentId] and [NodeId] in both [CTE] and [TheTable] so when you write

select [ParentId]
    , [NodeId]
    from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]

SQL Server doesn't know what [ParentId] and [NodeId] stands for (p or c)

You'd better write

WITH CTE AS (
    SELECT
        TheTable.ParentId,
        TheTable.NodeId
    FROM TheTable WHERE TheTable.ParentId = 1
    UNION ALL
    SELECT
        TheTable.ParentId,
        CTE.NodeId
    FROM CTE
    INNER JOIN TheTable ON TheTable.ParentId = CTE.NodeId
)

SELECT * FROM CTE

CodePudding user response：

The problem comes from multiple columns with the same name occuring in two tables.

Both your table and the CTE contain [ParentId] and [NodeId]. So when this statement is executed:

select [ParentId], [NodeId] from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]

The from contains both the [CTE] and [TheTable] and SQL cannot resolve in the SELECT which table it should pull those columns from. The link provided (copied and pasted, the link itself points to stackoverflow.com) uses * in the SELECT to give all columns and not worry about which table they originated from.

Either switch to a SELECT * style, or properly access the tables by table alias, i.e.:

select c.[ParentId], c.[NodeId] from [CTE] p, [TheTable] c where c.[ParentId] = p.[NodeId]

Add/Remove the specific table.column you'd like to UNION.

@Larnu Also points out correctly, the JOIN here is less than optimal.

Try:

select [ParentId], [NodeId] from [CTE] p INNER JOIN [TheTable] c ON c.[ParentId] = p.[NodeId]