How can I select all of the parents of rows that exist in another table?

I have two tables that share a many to many relationship. The relationships are stored in a third table.

CREATE TABLE Person
(
    Id int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE Filenodes
(
    Id hierarchyid NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE PersonFilenodes
(
    FileId hierarchyid NOT NULL,
    PersonId int NOT NULL,
    PRIMARY KEY (FileId, PersonId)
)

I need to select all of the filenodes that belong to a person, as well as all of the files that are ancestors of those files.

I know how to select all of the Filenodes that belong to a given person:

SELECT Id, Name
FROM PersonFilenodes
JOIN Filenodes on Filenodes.Id = PersonFilenodes.FileId
WHERE Id = @personId

Thanks to marc_s, I also know how to select all ancestors of a given filenode:

SELECT Id, Name
FROM Filenodes
WHERE @filenodeId.IsDescendantOf(Id) = 1

I just can't figure out how to combine the two queries to achieve the result set that I need. How can I select all of the filenodes that belong to a person, as well as all of the files that are ancestors of those files?

I think you need:

SELECT f.Id AS FileId, f.Name
FROM Filenodes AS f
WHERE EXISTS
      ( SELECT *
        FROM PersonFilenodes AS pf
        WHERE pf.PersonId = @personId 
          AND ( pf.FileId = f.Id
             OR pf.FileId.IsDescendantOf(f.id) = 1
              )
      ) ;

The inner query is a correlated subquery that takes two parameters: @personId, which is an actual parameter, and f.Id, which is the Filenode Id of the current row from the outer query.

StackExchange Database Administrators Q#128180, answer score: 4