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?

i checked and hadn't problem. please send table structure and your query?https://dbfiddle.uk/rpNp0Fe7 — abolfazl sadeghi, May 05 '23 at 13:55
@abolfazlsadeghi Thanks for your reply. I don't have an issue excluding the line with Yes in the value column. I'm trying to exclude the lines lower in the structure, position 2.1 and 2.2 to be exact. — TravelDuck, May 05 '23 at 13:59
thank you. why do you never remove 3,4 position Do you want to delete all the subsets that are no(if parent has Yes)? — abolfazl sadeghi, May 05 '23 at 14:04

Delta · Accepted Answer · 2023-05-05T14:14:23.747

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

score 0 · Answer 2 · answered May 05 '23 at 14:11

0

In this case you can use simple like with not exists or not in:

select * from table_name a 
where not exists (
  select 1 from table_name b 
  where a.position like concat(b.position, '.%') and value = 'YES')

dbfiddle demo

answered May 05 '23 at 14:11

Ponder Stibbons

14,723
2
21
24

Select records from structure excluding below specific records

2 Answers2