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