DECLARE @userTypeId BIGINT
SET @userTypeId = 1
WITH tblChild AS
(
SELECT *
FROM jt_PrdGroup WHERE ParentId = @userTypeId
UNION ALL
SELECT jt_PrdGroup.* FROM jt_PrdGroup JOIN tblChild ON jt_PrdGroup.ParentId = tblChild.Id
)
SELECT * FROM jt_Product p INNER JOIN (
SELECT *
FROM tblChild
UNION ALL
SELECT jt_PrdGroup.* FROM jt_PrdGroup WHERE jt_PrdGroup.Id = @userTypeId)
m1 ON m1.ID = p.Kind
