Skip to content

Instantly share code, notes, and snippets.

@nchammas
Created August 30, 2011 17:48
Show Gist options
  • Save nchammas/1181493 to your computer and use it in GitHub Desktop.
Save nchammas/1181493 to your computer and use it in GitHub Desktop.
Recursive T-SQL CTE to find root of arbitrarily deep hierarchy
-- schema
--DROP TABLE dbo.common_Text;
--DROP TABLE dbo.common_LanguageType;
--DROP TABLE dbo.common_Comment;
CREATE TABLE dbo.common_Comment (
CommentId BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE dbo.common_LanguageType (
LanguageId INT NOT NULL PRIMARY KEY
, Name NVARCHAR(100) NOT NULL
, Code NVARCHAR(2) NOT NULL
, DefaultId INT NOT NULL
, FOREIGN KEY (DefaultId) REFERENCES dbo.common_LanguageType(LanguageId)
);
CREATE TABLE dbo.common_Text (
TextId BIGINT NOT NULL PRIMARY KEY
, CommentId BIGINT NOT NULL
, Text NVARCHAR(500) NOT NULL
, LanguageId INT NOT NULL
, CommentTypeId INT NOT NULL
, FOREIGN KEY (LanguageId) REFERENCES dbo.common_LanguageType(LanguageId)
, FOREIGN KEY (CommentId) REFERENCES dbo.common_Comment(CommentId)
);
-- data
INSERT [dbo].[common_Comment] ([CommentId]) VALUES (1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (1, N'english', N'en', 1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (2, N'russian', N'ru', 1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (3, N'ukrainian', N'ua', 2)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (4, N'arabic', N'ar', 1)
INSERT [dbo].[common_LanguageType] ([LanguageId], [Name], [Code], [DefaultId]) VALUES (5, N'farsi', N'fr', 4)
INSERT [dbo].[common_Text] ([TextId], [CommentId], [Text], [LanguageId], [CommentTypeId]) VALUES (1, 1, N'english text', 1, 1)
INSERT [dbo].[common_Text] ([TextId], [CommentId], [Text], [LanguageId], [CommentTypeId]) VALUES (2, 1, N'wlik ehhh', 4, 1)
-- query
DECLARE @CommentId BIGINT = 1;
DECLARE @LanguageCode NVARCHAR(2) = 'ar';
WITH languages AS (
-- base case: language has required comment
SELECT
lt.LanguageId AS RootLanguageId
, lt.LanguageId
, lt.Code
, lt.DefaultId
, 0 AS Level
FROM
dbo.common_LanguageType lt
--WHERE
-- lt.LanguageId = lt.DefaultId
WHERE
EXISTS (
SELECT *
FROM dbo.common_Text t
WHERE
t.CommentId = @CommentId
AND t.LanguageId = lt.LanguageId
)
UNION ALL
-- recursive case: language is not its own default and
-- does not have the required comment
SELECT
l_default.RootLanguageId
, l.LanguageId
, l.Code
, l.DefaultId
, l_default.Level + 1 AS Level
FROM
dbo.common_LanguageType l
INNER JOIN languages l_default
ON l.DefaultId = l_default.LanguageId
WHERE
l.LanguageId <> l.DefaultId
AND NOT EXISTS (
SELECT *
FROM dbo.common_Text t
WHERE
t.CommentId = @CommentId
AND t.LanguageId = l.LanguageId
)
)
--SELECT *
--FROM languages;
SELECT t.Text
FROM
languages l
INNER JOIN dbo.common_Text t
ON l.RootLanguageId = t.LanguageId
WHERE
l.Code = @LanguageCode
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment