Joindre avec une requête récursive

Installer

J'ai les arrays suivants (simplement):

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); 

et voici quelques données de test:

 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); 

Qu'est-ce que je peux faire

 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; 

Cela me donne pour une personne une list de tous les TimeSlots auxquels cette personne est assignée et le nom de la catégorie feuille à laquelle appartient le TimeSlot. Par exemple pour une personne avec l'ID 401, il donne:

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

Avec la requête récursive suivante, je peux aussi get de la catégorie tous les ancêtres jusqu'à la catégorie racine:

 ;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; 

Par exemple pour la catégorie avec ID 111 je reçois:

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

Ce que je veux faire

Ce dont j'ai besoin, c'est d'une list de TimeSlots auxquels une personne est assignée, jointe avec les noms de catégories pour ce TimeSlot jusqu'à la catégorie racine. Donc, pour une personne ayant l'ID 401, le résultat devrait ressembler à ceci:

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

Je n'ai pas réussi à comprendre comment combiner les deux requêtes ci-dessus pour get le résultat attendu.

Ce que j'ai essayé

J'espérais que quelque chose dans ce sens pourrait fonctionner:

 ;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; 

La fonction définie par l'user et l' cross apply sont très utiles dans ce cas.

 --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; 

Cela traitera récursif les catégories et fournir toutes datatables basées sur un PersonId ou 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 

Il peut y avoir une meilleure façon d'écrire cela, mais fait ce que vous avez demandé et devrait vous amener où vous devez aller. Le 'BASE' ne remplit pas son objective initial, mais montre toujours la corrélation entre votre Personne et Catégorie, par exemple BASE 0 signifie que la catégorie de cet logging est assignée directement à la personne. Donc, je l'ai laissé pour ça. Merci.

J'espère que cela vous aide 🙂

 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 

DECLARE @PersonId int = 401;

IF OBJECT_ID ('tempdb.dbo. # TimeSlot') n'est pas null
DROP TABLE #TimeSlot

SELECT DISTINCT DENSE_RANK () OVER (COMMANDE PAR t.TimeSlotId) ID, t.TimeSlotId, c.CategoryId
INTO #TimeSlot
FROM PersonTimeSlotAssignment p
INNER JOIN TimeSlot t
ON p.TimeSlotId = t.TimeSlotId
INNER JOIN Catégorie c ON c.CategoryId = t.CategoryId
O Person PersonId = @ PersonId

IF OBJECT_ID ('tempdb.dbo. # Output') n'est pas null
DROP TABLE #Output
CREATE TABLE #Output (timeSlotId INT, CategoryId INT)
DECLARE @id INT = 1 DECLARE @timeSlotId COMME DECLARE @CategoryId INT
WHILE (SELECT ID FROM #TimeSlot WHERE id = @ id) N'EST PAS NUL
COMMENCER
SELECT @ timeSlotId = TimeSlotId, @ CategoryId = CategoryId FROM #TimeSlot WHERE ID = @ id
INSERT INTO #Output SELECT @ timeSlotId, @ CategoryId
WHILE (SELECT ParentCategoryId FROM Catégorie WHERE CategoryId = @ CategoryId) n'est pas null
COMMENCER
SELECT @ CategoryId = ParentCategoryId FROM Catégorie WHERE CategoryId = @ CategoryId
INSERT INTO #Output SELECT @ timeSlotId, @ CategoryId
FIN
SET @ id = @ id + 1
FIN

SELECT a.timeSlotId, c.Name FROM #Sortie une INNER JOIN Catégorie c ON a.CategoryId = c.CategoryId