1

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
John Son
  • 31
  • 8
  • Why are you *still* using that ANSI-89 implicit JOIN syntax? The ANSI-92 explicit JOIN syntax has been around for **30 years** now. [Bad Habits to Kick : Using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Thom A Oct 31 '22 at 09:56
  • 1
    You're using `SELECT *` so you are returning *more* columns in the bottom query; stop using `SELECT *` and get into the good habit of defining your columns. As for the former error, does your table `TheTable` (which you oddly alias as `c`) have a column called `NodeId`? If not, that's why you're getting the error. – Thom A Oct 31 '22 at 09:58
  • ah, i see. i will select the column and try again. – John Son Oct 31 '22 at 10:00
  • hmm. this doesn't work. – John Son Oct 31 '22 at 10:01
  • 2
    "This doesn't work"? What is "this"? what is "doesn't work"? – Thom A Oct 31 '22 at 10:02
  • *Now* you have ambiguous columns in your bottom query. `select [ParentId], [NodeId]` Is that "C" for `TheTable`'s `ParentId` or "P" for CTE's `ParentId`? Same for `NodeId`. – Thom A Oct 31 '22 at 10:05
  • just update. the new queries, doesn't work same error occur. – John Son Oct 31 '22 at 10:05
  • Your link doesn't resolve to anything, but the first thing you need to do is confirm that just this works without error: `select [ParentId] , [NodeId] from [TheTable] c where c.[ParentId] = 1` – Nick.Mc Oct 31 '22 at 10:06

2 Answers2

0

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]

I edited this because of a discussion with @Delta32000 where it's better to explicitly say INNER JOIN instead of JOIN even though they are functionally equivalent in T-SQL. Thanks for the clarification!

Avogadro
  • 353
  • 2
  • 11
  • I don't think doing a `CROSS JOIN` here `[CTE] p, [TheTable]` is a good idea – Delta Oct 31 '22 at 10:20
  • @Delta32000 can you give some reasoning behind this? I could go either way. I prefer a certain style but wouldn’t say the OP was wrong with their syntax. – Avogadro Oct 31 '22 at 10:49
  • 1
    @John is trying to do a recursive function here. In this case you want to `JOIN` on a certain condition. A `CROSS JOIN` will do a cartesian product betwin tables then keep only the correct row, that will lead to performances issues (what you don't want to see in a recursive function). The `INNER JOIN`, that don't do the cartesian product will be better in this solution. I think any query is wrong if it leads to performance issues. – Delta Oct 31 '22 at 13:00
  • 1
    @Delta32000 Thanks for clarifying! I did need to ask because the `JOIN` statement is just an `INNER JOIN` and not a `CROSS JOIN` in T-SQL. So I believe I was still ok, but I'll err on the side of being more explicit. – Avogadro Oct 31 '22 at 13:13
  • 1
    Yup you are right @Avogadro ! My bad if I haven't been clear about the fact that I was talking about the comma that means `CROSS JOIN` and not the `JOIN` for `INNER JOIN`. :) – Delta Oct 31 '22 at 13:26
0

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
Delta
  • 551
  • 2
  • 16