4

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?

0
4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.