Join with recursive query

Question

Setup

I have the following tables (simplyfied):

CREATE TABLE Category(
    CategoryId              int           NOT NULL PRIMARY KEY,
    ParentCategoryId        int           NULL,
    Name                    nvarchar(255) NOT NULL,
    FOREIGN KEY (ParentCategoryId) REFERENCES Category(CategoryId) ON UPDATE NO ACTION ON DELETE NO ACTION);

CREATE TABLE TimeSlot(
    TimeSlotId              int           NOT NULL PRIMARY KEY,
    CategoryId              int           NOT NULL,
    FOREIGN KEY (CategoryId) REFERENCES Category(CategoryId) ON UPDATE NO ACTION ON DELETE NO ACTION);

CREATE TABLE PersonTimeSlotAssignment(
    PersonId                int           NOT NULL,
    TimeSlotId              int           NOT NULL,
    PRIMARY KEY (PersonId, TimeSlotId),
    FOREIGN KEY (TimeSlotId) REFERENCES TimeSlot(TimeSlotId) ON UPDATE NO ACTION ON DELETE NO ACTION);

and here is some test data:

INSERT INTO Category(CategoryId, ParentCategoryId, Name) VALUES (100, NULL, 'cat 1');
INSERT INTO Category(CategoryId, ParentCategoryId, Name) VALUES (110, 100, 'cat 1.1');
INSERT INTO Category(CategoryId, ParentCategoryId, Name) VALUES (111, 110, 'cat 1.1.1');
INSERT INTO Category(CategoryId, ParentCategoryId, Name) VALUES (120, 100, 'cat 1.2');
INSERT INTO Category(CategoryId, ParentCategoryId, Name) VALUES (200, NULL, 'cat 2');

INSERT INTO TimeSlot(TimeSlotId, CategoryId) VALUES (301, 111);
INSERT INTO TimeSlot(TimeSlotId, CategoryId) VALUES (302, 120);
INSERT INTO TimeSlot(TimeSlotId, CategoryId) VALUES (303, 200);

INSERT INTO PersonTimeSlotAssignment(PersonId, TimeSlotId) VALUES (401, 301);
INSERT INTO PersonTimeSlotAssignment(PersonId, TimeSlotId) VALUES (401, 302);
INSERT INTO PersonTimeSlotAssignment(PersonId, TimeSlotId) VALUES (402, 302);
INSERT INTO PersonTimeSlotAssignment(PersonId, TimeSlotId) VALUES (402, 303);

What I can do

SELECT ts.TimeSlotId, pc.Name 
    FROM PersonTimeSlotAssignment 
    JOIN TimeSlot AS ts ON PersonTimeSlotAssignment.TimeSlotId = ts.TimeSlotId 
    JOIN Category AS pc ON ts.CategoryId = pc.CategoryId
    WHERE PersonTimeSlotAssignment.PersonId = @PERSON_ID;

This gives me for some person a list of all TimeSlots to which this person is assigned and the name of the leaf category which the TimeSlot belongs to. For example for person with ID 401 it gives:

TimeSlotId  Name
---------------------
301         cat 1.1.1
302         cat 1.2

With the following recursive query I can also get from some category all the ancestors up to the root category:

;WITH Parents AS (
    SELECT * FROM Category 
        WHERE CategoryId=@CATEGORY_ID
        UNION ALL SELECT c.* FROM Category c JOIN Parents p ON p.ParentCategoryId=c.CategoryId
    ) 
    SELECT Name FROM Parents;

For example for category with ID 111 I get:

Name
---------
cat 1.1.1
cat 1.1
cat 1

What I want to do

What I need is a list of TimeSlots a person is assigned with, joined with the category names for that TimeSlot up to the root category. So for person with ID 401 the result should look like this:

TimeSlotId  Name
---------------------
301         cat 1.1.1
301         cat 1.1
301         cat 1
302         cat 1.2
302         cat 1

I was not able to figure out how to combine the above two queries so that I get the expected result.

What I tried

I was hoping that something along these lines could work:

;WITH Parents AS (
    SELECT * FROM Category 
        WHERE CategoryId=<<'How to get CategoryId for each assigned TimeSlot here?'>>
        UNION ALL SELECT c.* FROM Category c JOIN Parents p ON p.ParentCategoryId=c.CategoryId
    ) 
SELECT ts.TimeSlotId, pc.Name 
    FROM PersonTimeSlotAssignment 
    JOIN TimeSlot AS ts ON PersonTimeSlotAssignment.TimeSlotId = ts.TimeSlotId 
    JOIN Parents AS pc ON <<'How should this look like?'>>
    WHERE PersonTimeSlotAssignment.PersonId = @PERSON_ID;

Answer 1

This will handle recursing the categories and providing all the data based on a PersonId or TimeSlotId:

WITH Categories (PersonId, CategoryId, ParentCategoryId, TimeSlotId, Name, BASE)
AS
(
SELECT PersonId, c.CategoryId, c.ParentCategoryId, pts.TimeSlotId, c.Name, 0 AS BASE
FROM Category c
INNER JOIN TimeSlot ts ON c.CategoryId = ts.CategoryId
INNER JOIN PersonTimeSlotAssignment pts ON ts.TimeSlotId = pts.TimeSlotId
UNION ALL
SELECT PersonId, pc.CategoryId, pc.ParentCategoryId, TimeSlotId, pc.Name, BASE + 1
FROM Category pc
INNER JOIN Categories cs ON cs.ParentCategoryId = pc.CategoryId
)
SELECT * FROM Categories 
WHERE PersonId = 401
--WHERE TimeSlotId = 301

There may be a better way to write this, but does what you've asked and should get you where you need to go. The 'BASE' does not serve its original purpose, but does still show the correlation between your Person and Category, e.g. BASE 0 means the category from that record is assigned directly to the person. So, I left it for that. Thanks.

Answer 2

DECLARE @PersonId int =401;

IF OBJECT_ID('tempdb.dbo.#TimeSlot') is not null
DROP TABLE #TimeSlot

SELECT DISTINCT DENSE_RANK() OVER(ORDER BY t.TimeSlotId ) ID, t.TimeSlotId,c.CategoryId
INTO #TimeSlot
FROM PersonTimeSlotAssignment p
INNER JOIN TimeSlot t
ON p.TimeSlotId=t.TimeSlotId
INNER JOIN Category c ON c.CategoryId=t.CategoryId
WHERE PersonId=@PersonId

IF OBJECT_ID('tempdb.dbo.#Output') is not null
DROP TABLE #Output
CREATE TABLE #Output(timeSlotId INT,CategoryId INT)
DECLARE @id INT=1 DECLARE @timeSlotId AS INT DECLARE @CategoryId INT
WHILE( SELECT id FROM #TimeSlot WHERE id=@id) IS NOT NULL
BEGIN
SELECT @timeSlotId=TimeSlotId,@CategoryId=CategoryId FROM #TimeSlot WHERE ID=@id
INSERT INTO #Output SELECT @timeSlotId,@CategoryId
WHILE( SELECT ParentCategoryId FROM Category WHERE CategoryId=@CategoryId) is not null
BEGIN
SELECT @CategoryId=ParentCategoryId FROM Category WHERE CategoryId=@CategoryId
INSERT INTO #Output SELECT @timeSlotId,@CategoryId
END
SET @id=@id+1
END

SELECT a.timeSlotId,c.Name FROM #Output a INNER JOIN Category c ON a.CategoryId=c.CategoryId

Answer 3

User defined function and cross apply is very useful in this case.

--1. Create function
create function fn_Category(@id int)
returns table
as
return
with tbl as (
--anckor query
select CategoryId, ParentCategoryId,Name, 1 lvl
from Category where CategoryId = @id
union all
--recursive query
select c.CategoryId, c.ParentCategoryId,c.Name, lvl+1
from Category c
inner join tbl on tbl.ParentCategoryId=c.CategoryId--go up the tree
)
select * from tbl
go
--end of function

--2. and now we can use it
declare @PERSON_ID int = 401
SELECT ts.TimeSlotId, pc.Name 
    FROM PersonTimeSlotAssignment 
    JOIN TimeSlot AS ts ON PersonTimeSlotAssignment.TimeSlotId = ts.TimeSlotId 
    --JOIN Category AS pc ON ts.CategoryId = pc.CategoryId
    --use cross apply instead
    cross apply fn_Category(ts.CategoryId) pc 
    WHERE PersonTimeSlotAssignment.PersonId = @PERSON_ID;

Answer 4

Hope this helps you :)

DECLARE @PersonId INT= 401;

WITH CTE AS
(
SELECT 
    t.*,
    c.CategoryId AS CategoryId_c,
    c.ParentCategoryId as ParentCategoryId_c,
    c.Name AS Name_c,
    c1.CategoryId AS CategoryId_c2,
    c1.ParentCategoryId AS ParentCategoryId_c2,
    c1.Name AS Name_c2,
    c2.CategoryId as CategoryId_c3,
    c2.ParentCategoryId AS ParentCategoryId_c3,
    c2.Name AS Name_c3
FROM 
PersonTimeSlotAssignment p
INNER JOIN TimeSlot t ON t.TimeSlotId=p.TimeSlotId
INNER JOIN Category c ON t.CategoryId=c.CategoryId
LEFT JOIN Category c1 ON c1.CategoryId=c.ParentCategoryId
LEFT JOIN Category c2 ON c2.CategoryId=c1.ParentCategoryId
WHERE p.PersonId=@PersonId
)

SELECT * FROM (
    SELECT TimeSlotId,Name_c  FROM CTE 
    UNION
    SELECT TimeSlotId,Name_c2 FROM CTE 
    UNION
    SELECT TimeSlotId,Name_c3 FROM CTE 
)a WHERE Name_c IS NOT NULL