eyalewin eyalewin - 3 months ago 7x
SQL Question

Hierarchical ToDoList as Adjacency List

I'm building a todo list with a hierarchical structure , and I was searching for a good logic for it and found out that it is with Adjacency List.

The Goal is to be able to do a to-do list with hierarchical tasks (one task is the root, and then can have nodes, the nodes can have their own nodes and so on..)
So I built a table

CREATE TABLE [dbo].[Tasks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Owner] [nvarchar](max) NOT NULL,
[Parent] [int] NULL,

after inserting data the table looks like this:

enter image description here

And I want a query that shows me all the roots, and if some root has nodes, so show it as well

my query is

SELECT t.*, COUNT(tr.Parent) as SubCount
FROM Tasks t
INNER JOIN Tasks tr on t.Id = tr.Parent
GROUP BY t.Name, t.Owner, t.Id, t.Parent
SELECT t.*, 0 as SubCount
FROM Tasks t
where Parent IS NULL;

but the result is:

enter image description here

expected result should show only the rows with Parent IS NULL and the rows that have sub nodes, for example:

enter image description here

What should be the right query here ??


Try this,

    (SELECT COUNT(IT.Id) FROM Tasks IT WHERE IT.Parent = T.Id) AS SubCount
    Tasks T
    T.Parent IS NULL