Recursive select that selects rows based own plus childrens values

I need to select rows in a table like this:

Select all rows in the table where both conditions are met:

• Condition 1: the value column should not match with any value in table v

• Condition 2: no decendent (on any level, ie: child or sub child, sub- sub- child etc) has a value that matches with any value in table v

Table v looks like this:

Expected result from example table. Should [row] be selected/returned?

• a1: No – condition 2
• a2: No – condition 2
• a3: No – condition 1
• a4: No – condition 1
• a5: Yes – (value does not match in v and no decendents that match in v)
• a6: Yes – (value does not match in v and no decendents that match in v)
• a7: Yes – (value does not match in v and no decendents that match in v)
• a8: Yes – (value does not match in v and no decendents that match in v)

Here is an sqlfiddle where the tables are set up together with a recursive function that shows all rows and their level in the tree, but that I don’t know how to procede with:

http://sqlfiddle.com/#!18/736a28/15/0

Answer

Check this solution:

--------------------------- DDL+DML
drop table if exists a
drop table if exists v
GO

CREATE TABLE a
    ([id] varchar(13), [parentId] varchar(57), [value] varchar(57))
;

CREATE TABLE v
    ([id] varchar(13), [value] varchar(57))
;

INSERT INTO a
    ([id], [parentId], [value])
VALUES
    ('a1', NULL, NULL),
    ('a2', 'a1', NULL),
    ('a3', 'a2', '1'),
    ('a4', NULL, '5'),
    ('a5', 'a1', '8'),
    ('a6', 'a2', NULL),
    ('a7', NULL, NULL),
    ('a8', NULL, '3'),
    ('a9', 'a8', '7')
;

INSERT INTO v
    ([id], [value])
VALUES
    ('v1', '1'),
    ('v2', '5'),
    ('v3', '10'),
    ('v4', '15'),
    ('v5', '20'),
    ('v6', '25'),
    ('v7', '30'),
    ('v8', '35'),
    ('v9', '40')
;

SELECT * FROM a
SELECT * FROM v
GO

-------------------- Solution

WITH MyRecCTE AS(
    SELECT a.id, a.parentId, a.[value], Res = 'NO'
    FROM a
    INNER JOIN v ON a.[value] = v.[value]
    
    UNION ALL

    SELECT 
        a.id, a.parentId, a.[value], Res = 'NO'
    FROM a
    INNER JOIN MyRecCTE c ON c.parentId = a.id
)
SELECT DISTINCT a.id, a.parentId,a.[value], ISNULL(CONVERT(VARCHAR(3),c.Res),'YES')
FROM a
LEFT JOIN MyRecCTE c ON a.id = c.id
ORDER BY id
GO

Result Set (fits requested)):

For the sake of the discussion let’s add another row which lead rows with id a8 and a9 to be “NO” since it is child of a9 and has value from the second table

INSERT INTO a
    ([id], [parentId], [value])
VALUES
    ('a10', 'a9', 35)
GO

test 2 Result set (fits expected)