SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create the table
CREATE TABLE MyTable (
    folderId INT PRIMARY KEY,
    parentFolderId INT NULL,
    myColumn VARCHAR(255)
);
-- Insert the data
INSERT INTO MyTable (folderId, parentFolderId, myColumn) VALUES
(1, NULL, 'MyValue1'),
(2, NULL, 'MyValue1'),
(3, 1, 'MyValue1'),
(4, 3, 'MyValue2'),
(6, NULL, 'MyValue1'),
(7, NULL, 'MyValue2'),
(10, 6, 'MyValue1'),
(11, 10, 'MyValue1'),
(12, 7, NULL);
9 rows affected

SELECT t1.*
FROM MyTable t1
WHERE t1.myColumn = 'MyValue1'
AND NOT EXISTS
(
    SELECT *
    FROM MyTable t2
    WHERE t1.parentFolderId = t2.folderId
    AND t2.parentFolderId IS NOT NULL
);


folderId
parentFolderId
myColumn


1
null
MyValue1

2
null
MyValue1

3
1
MyValue1

6
null
MyValue1

10
6
MyValue1

folderId	parentFolderId	myColumn
1	null	MyValue1
2	null	MyValue1
3	1	MyValue1
6	null	MyValue1
10	6	MyValue1