Join between three tables where data is in Comma values row ?

First Create a TABLE VALUED function in the sql server as mentioned below :-

CREATE FUNCTION [dbo].[SplitBySeparator]
(
    @Value	NVARCHAR(MAX),
    @Spliter	NVARCHAR(5)
) 
RETURNS @ReturnValues TABLE
(
    Value		NVARCHAR(MAX)
)
AS 
BEGIN
	-- iterate through the values
	WHILE (CHARINDEX(@Spliter,@Value)>0)
		BEGIN
			INSERT INTO @ReturnValues (value)
			SELECT
				 Value = LTRIM(RTRIM(SUBSTRING(@Value,1,CHARINDEX(@Spliter,@Value)-1)))
				 SET @Value = SUBSTRING(@Value,CHARINDEX(@Spliter,@Value)+LEN(@Spliter),LEN(@Value))
		END
	
	-- insert records into result table
	INSERT INTO @ReturnValues (Value)
	SELECT Value = LTRIM(RTRIM(@Value))
	RETURN
	
END

This function is used for splitting the value passed by the splitter character we are passing as second parameter and then we can do as sample script written below :-

Example :-

DECLARE @SubCategoryID INT = 5
DECLARE @Test TABLE(
	ScreenName VARCHAR(50),
	SubCategoryIDs VARCHAR(MAX)
)

INSERT INTO @Test
VALUES('Forever22', '2,5,10,12,13,25')

INSERT INTO @Test
VALUES('war', '4')

INSERT INTO @Test
VALUES('Rinku', '8,5')

INSERT INTO @Test
VALUES('abcdefg', '8,5')

SELECT * FROM @Test WHERE @SubCategoryID IN(SELECT * FROm [dbo].[SplitBySeparator](SubCategoryIDs, ','))

This is just an example how we can accomplish your requirements and explained with a temporary table. Hope this will definitely of help for you.