How do I join these tables?

Other SQL Server Topics
1

Hi, I have two select statements that I union together but there's only one table difference and I feel like I should be able to put them together. I have an example of something I tried which is below but it feels janky, I feel like there should be a better way than doing all the extra union and joining.

SELECT RG1.GROUP_ID
FROM ENTITY E1
INNER JOIN GROUP_PERMISSION A1 ON E1.ID = A1.ENTITY_ID
INNER JOIN RecursiveGroups AS RG1 ON A1.ID = RG1.GROUP_ID
WHERE E1.ID = @EntityID

UNION

SELECT RG2.GROUP_ID
FROM ENTITY E2
INNER JOIN TYPE_GROUP_ASSIGNMENT A2 ON E2.ENTITY_TYPE_ID = A2.ENTITY_TYPE_ID
INNER JOIN RecursiveGroups AS RG2 ON A2.ID = RG2.GROUP_ID
WHERE E2.ID = @EntityID;

janky possible solution

SELECT DISTINCT RG.GROUP_ID
FROM [CONF].[TBL_MATRIX_ENTITY] E
INNER JOIN GROUP_PERMISSION AS A ON E.ID = A.ENTITY_ID
INNER JOIN TYPE_GROUP_ASSIGNMENT AS C ON E.ENTITY_TYPE_ID = C.ENTITY_TYPE_ID
INNER JOIN RecursiveGroups AS RG1 ON A.GROUP_ID = RG.GROUP_ID OR C.GROUP_ID = RG.GROUP_ID
WHERE E.ID = @EntityID

image

2

I don't know if your possible solution works or doesn't work but it isn't janky.

3 
SELECT /*DISTINCT*/ RG.GROUP_ID
FROM RecursiveGroups RG
WHERE EXISTS
(
	SELECT 1
	FROM GROUP_PERMISSION GP
	WHERE GP.ID = RG.GROUP_ID
		AND GP.[ENTITY_ID] = @EntityID
/*
		AND EXISTS
		(
			SELECT 1
			FROM ENTITY E1
			WHERE E1.ID = GP.[ENTITY_ID]
		)
*/
) OR EXISTS
(
	SELECT 1
	FROM TYPE_GROUP_ASSIGNMENT TGA
	WHERE TGA.ID = RG.GROUP_ID
		AND EXISTS
		(
			SELECT 1
			FROM ENTITY E2
			WHERE E2.ENTITY_TYPE_ID = TGA.ENTITY_TYPE_ID
				AND E2.ID = @EntityID
		)
);