Recursive T-SQL CTE to find root of arbitrarily deep hierarchy

default_language.sql

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