Select records from structure excluding below specific records

Question

I have the need to select records from a SQL database but skip all records below records with specific values in the column.

Example data from multiple joined tables:

POSITION    CODE        VALUE
1           A001        No
2           A002        Yes
2.1         A005        No
2.2         A006        No
3           A003        No
4           A004        No
4.1         A007        No

I would like that positions 2.1 and 2.2 to be excluded from the result because they reside below the record with VALUE=Yes:

POSITION    CODE        VALUE
1           A001        No
2           A002        Yes
3           A003        No
4           A004        No
4.1         A007        No

WHERE VALUE<>'YES' Just leaves out the lines which contain this value. I don't know how to exclude the lines below.

The value Yes can exist on any level in the structure. How can I exclude those lines below?

Answer 1

Depending on your use case, you can use hierachical data type :

CREATE TABLE #TMP (
    POSITION HIERARCHYID,
    CODE CHAR(4),
    VAL BIT
);

INSERT INTO #TMP
VALUES
    ('/1/', 'A001', 0),
    ('/2/', 'A002', 1),
    ('/2/1/', 'A005', 0),
    ('/2/2/', 'A006', 0),
    ('/3/', 'A003', 0),
    ('/4/', 'A004', 0),
    ('/4/1/', 'A007', 0)

;WITH CTE AS
(
    SELECT
        POSITION,
        CODE,
        VAL
    FROM #TMP
    WHERE POSITION.GetAncestor(1) = HIERARCHYID::Parse('/')

    UNION ALL

    SELECT
        #TMP.POSITION,
        #TMP.CODE,
        #TMP.VAL
    FROM #TMP
    INNER JOIN CTE
        ON #TMP.POSITION.IsDescendantOf(CTE.POSITION) = 1
        AND #TMP.POSITION != CTE.POSITION
    WHERE CTE.VAL != 1
)
SELECT
    CAST(POSITION AS NVARCHAR(100)) AS POSITION,
    CODE,
    CASE VAL WHEN 1 THEN 'Yes' ELSE 'No' END AS VALUE
FROM CTE

DROP TABLE #TMP

Result :

POSITION	CODE	VALUE
/1/	A001	NO
/2/	A002	YES
/3/	A003	NO
/4/	A004	NO
/4/1/	A007	NO