Filtering out children in a table with parentid

Question

Hi,

I need a bit of help constructing a query that will let me filter the following data.

Table: MyTree
Id  ParentId  Visible
=====================
1   null      0
2   1         1
3   2         1
4   3         1
5   null      1
6   5         1

I expect the following result from the query:

Id  ParentId  Visible
=====================
5   null      1
6   5         1

That is, all the children of the hidden node should not be returned. What's more is that the depth of a hierarchy is not limited. Now don't answer "just set 2, 3 & 4 to visible=0" for non-obviuos reasons that is not possible... Like I'm fixing a horrible "legacy system".

I was thinking of something like:

SELECT *
FROM MyTree m1
JOIN MyTree m2 ON m1.ParentId = m2.Id
WHERE m1.Visible = 1
AND (m1.ParentId IS NULL OR m2.Id IS NOT NULL)

Sorry for any syntactical mistakes

But that will only filter the first level, right? Hope you can help.

Edit: Finished up the title, whoops. The server is a brand spanking new MSSQL 2008 server but the database is running in 2000 compatibility mode.

Answer 1

In SQL Server 2005+:

WITH    q (id, parentid, visible) AS
        (
        SELECT  id, parentid, visible
        FROM    mytree
        WHERE   id = 5
        UNION ALL
        SELECT  m.id, m.parentid, m.visible
        FROM    q
        JOIN    mytree m
        ON      m.parentid = q.id
        WHERE   q.visible = 1
        )
SELECT  *
FROM    q

Answer 2

I don't think what you need is possible from a single query. This looks more like something to do from code and still it will require multiple queries to DB.

If you really need to do it from SQL I think your best bet would be to use a cursor and build a table with hidden IDs. If data doesn't change often you might keep that 'temporary' table as a kind of cache.

Edit: I stand corrected (for SQL 2005) and also learned something new today :)

Answer 3

I agree with @Quassnoi's focus on recursive CTEs (in SQL Server 2005 or later) but I think the logic is different to answer the original question:

WITH visall(id, parentid, visible) AS
   (SELECT  id, parentid, visible
    FROM    mytree
    WHERE   parentid IS NULL
        UNION ALL
    SELECT  m.id, m.parentid, m.visible & visall.visible AS visible
    FROM    visall
    JOIN    mytree m
      ON    m.parentid = visall.id
   )
SELECT  *
FROM    visall
WHERE   visall.visible = 1

A probably more optimized way to express the same logic should be to have the visible checks in the WHERE as much as possible -- stop recursion along invisible "subtrees" ASAP. I.e.:

WITH visall(id, parentid, visible) AS
   (SELECT  id, parentid, visible
    FROM    mytree
    WHERE   parentid IS NULL AND visible = 1
        UNION ALL
    SELECT  m.id, m.parentid, m.visible
    FROM    visall
    JOIN    mytree m
      ON    m.parentid = visall.id
    WHERE   m.visible = 1
   )
SELECT  *
FROM    visall

As usual with performance issues, benchmarking both versions on realistic data is necessary to decide with confidence (it also helps to check that they do indeed produce identical results;-) -- as DB engines' optimizers sometimes do strange things for strange reasons;-).

Answer 4

I think Quassnoi was close to what the questioner wants, but not quite. I think this is what the questioner is looking for (SQL Server 2005+):

WITH    q (id) AS
        (
        SELECT  id
        FROM    mytree
        WHERE   parentid is null and visible=1
        UNION ALL
        SELECT  m.id
        FROM    q
        JOIN    mytree m
        ON      m.parentid = q.id
        WHERE   q.visible = 1
        )
SELECT  *
FROM    q

Common Table Expressions are great for this kind of work.