mercredi 15 juin 2016

Join with recursive query

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;

Aucun commentaire:

Enregistrer un commentaire