SELECT récursif utilisant JOIN à partir de 2 tables

J'ai 2 tables: Utilisateurs et Rôles .

Colonnes de la table des users:

UserId FirstName Lastname

Rôles des tables de rôles:

 RoleId UserId ParentId

Je veux être capable d'extraire la date de ces 2 tables représentant une colonne nommée ParentName .

Disons donc que j'ai ces données:

Table des users:

 UserId FirstName Lastname 1 John Doe 2 Jane Smith 3 John Smith

Table des rôles:

 RoleId UserId ParentId 1 1 NULL 2 2 1 3 3 2

Donc je veux avoir cette table:

 UserId FirstName Lastname RoleId ParentId ParentName 1 John Doe 1 NULL NULL 2 Jane Smith 2 1 John Doe 3 John Smith 3 2 Jane Smith

J'ai essayé mais je ne pouvais pas. J'ai essayé INNER JOIN, OUTER JOIN, Sous-requêtes mais je ne pouvais pas get ce que je voulais.

En joignant 2 tables set, j'ai ceci:

 SELECT UserId, FirstName, Lastname, RoleId, ParentId FROM Users INNER JOIN Roles ON Users.UserId = Roles.UserId

Et en rejoignant une table Rôles auto-récursive, j'ai ceci:

 SELECT ChildUsers.UserId, ChildUsers.RoleId, ParentUsers.UserId, ParentUsers.RoleId, Users.LastName FROM Roles AS ChildUsers LEFT JOIN Roles AS ParentUsers ON ChildUsers.ParentId = ParentUsers.RoleId INNER JOIN Users ON Users.UserId = ParentUsers.UserId

Mais je ne pouvais pas get ce que je voulais. Comment puis-je l'get?

———–METTRE À JOUR—————

Voici ma propre solution:

 SELECT Roles.RoleID, LastName, Parent.ParentName FROM Roles INNER JOIN Users ON Users.UserID = Roles.UserID LEFT JOIN (SELECT LastName AS ParentName, RoleID FROM Users INNER JOIN Roles ON Users.UserID = Roles.UserID) Parent ON Parent.RoleID = Roles.ParentID

Mais je choisis la solution LukStorms comme réponse.

Pour get ce résultat attendu?

Si le ParentId contient RoleId alors cela devrait fonctionner:

 SELECT r.UserId, u.FirstName, u.Lastname, r.RoleId, r.ParentId, pu.FirstName+' '+pu.LastName AS ParentName FROM Roles r LEFT JOIN Users u ON r.UserId = u.UserId LEFT JOIN Roles pr on r.ParentId = pr.RoleId LEFT JOIN Users pu on pr.UserId = pu.UserId

Mais si ParentId est une key étrangère à la table Users:

 SELECT r.UserId, u.FirstName, u.Lastname, r.RoleId, r.ParentId, p.FirstName+' '+p.LastName AS ParentName FROM Roles r LEFT JOIN Users u ON r.UserId = u.UserId LEFT JOIN Users p on r.ParentId = p.UserId

En utilisant cette requête, vous pouvez get le résultat que vous voulez. Puisque vous voulez seulement une profondeur de 2 niveaux, vous pouvez le faire sans requêtes récursives

 DECLARE @Users TABLE (UserId INT, FirstName VARCHAR(255), LastName VARCHAR(255)) INSERT INTO @Users VALUES (1,'John','Doe'), (2,'Jane','Smith'), (3,'John','Smith') DECLARE @ROles TABLE (RoleId INT, UserId INT, ParentId INT) INSERT INTO @ROles VALUES (1,1,NULL), (2,2,1), (3,3,2) SELECT u.UserId, u.FirstName, u.LastName, r.RoleId, r.ParentId, p.ParentName FROM @Users AS u LEFT OUTER JOIN @Roles AS r ON r.UserId = u.UserId LEFT OUTER JOIN (SELECT r.UserId AS ParentId, up.FirstName + ' ' + up.LastName AS ParentName FROM @ROles AS r INNER JOIN @Users AS up ON up.UserId = r.ParentId) AS p ON p.ParentId = u.UserId

Mise à jour: append un résultat

 UserId FirstName LastName RoleId ParentId ParentName 1 John Doe 1 NULL NULL 2 Jane Smith 2 1 John Doe 3 John Smith 3 2 Jane Smith

Essayez cette requête: –

 Create table Users (UserId INT, FirstName VARCHAR(255), LastName VARCHAR(255)); INSERT INTO Users VALUES(1,'John','Doe'); INSERT INTO Users VALUES(2,'Jane','Smith'); INSERT INTO Users VALUES(3,'John','Smith'); Create table Roles(RoleId INT, UserId INT, ParentId INT); INSERT INTO ROles VALUES(1,1,NULL); INSERT INTO ROles VALUES(2,2,1); INSERT INTO ROles VALUES(3,3,2); SELECT a.*,concat(b.FirstName,' ',b.Lastname) as ParentName from ( SELECT a.UserId, FirstName, Lastname, RoleId, ParentId FROM Users a INNER JOIN Roles b ON a.UserId = b.UserId ) a left join Users b on a.ParentId=b.userid;

Sortie:-

  UserId FirstName Lastname RoleId ParentId ParentName 1 1 John Doe 1 NULL 2 2 Jane Smith 2 1 John Doe 3 3 John Smith 3 2 Jane Smith

Code expliqué:

CTE pour get les détails de l'user et les rôles.

LEFT JOIN avec le CTE pour get les informations du parent. Rejoindre par l'ID ParentId de l'user avec l'ID user Parents (qui est également un user) pour get le ParentName.

(Note: Vous pouvez exécuter le cte séparément sans la seconde jointure pour voir la sortie, puis exécutez le tout pour voir comment il se joint.)

 CREATE TABLE [dbo].[Roles]( [RoleId] [int] NULL, [UserId] [int] NULL, [ParentId] [int] NULL ) ; CREATE TABLE [dbo].[Users]( [UserId] [int] NULL, [FirstName] [varchar](20) NULL, [Lastname] [varchar](20) NULL ); INSERT [dbo].[Roles] ([RoleId], [UserId], [ParentId]) VALUES (1, 1, NULL); INSERT [dbo].[Roles] ([RoleId], [UserId], [ParentId]) VALUES (2, 2, 1); INSERT [dbo].[Roles] ([RoleId], [UserId], [ParentId]) VALUES (3, 3, 2); INSERT [dbo].[Users] ([UserId], [FirstName], [Lastname]) VALUES (1, N'John', N'Doe'); INSERT [dbo].[Users] ([UserId], [FirstName], [Lastname]) VALUES (2, N'Jane', N'Smith'); INSERT [dbo].[Users] ([UserId], [FirstName], [Lastname]) VALUES (3, N'John', N'Smith'); with cteUsers AS ( select u.UserId, u.FirstName, u.Lastname, r.RoleId, r.ParentId from Users AS u inner join Roles AS r on u.UserId=r.UserId ) select cteUsers.UserId ,cteUsers.FirstName ,cteUsers.Lastname ,cteUsers.RoleId ,cteUsers.ParentId ,(p.FirstName + ' ' + p.Lastname) AS ParentName from cteUsers left join Users as p on cteUsers.ParentId=p.UserId ;

Exécutez ce script en direct