eyalewin eyalewin - 4 months ago 14
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
UNION
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 ??

Answer

Try this,

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