Select statement to do a left join between tables, while filtering the results based on specific values in the right table?

I need an SQL Server Select statement to perform a left join between tables that filters the results based on specific values in the right table.

Here is an example:
The left table will hold color values. The right table will hold rows for the color combination of the left table.

tblObject /*this is the left table */
objID    objName
1        Violet
2        Rose 
3        Grass
4        Sky
5        Magenta

tblColorCombination /*this is the right table */
ccID    objID     Color
1       1         Red
2       1         Blue
3       2         Red
4       3         Green
5       4         Blue
6       5         Blue
7       5         Red

The select should return the tblObject rows with 'Red', 'Blue' combination in the tblColorCombination

ccID    obj ID    objName   Color
1       1         Violet    Red
2       1         Violet    Blue
6       5         Magenta   Blue
7       5         Magenta   Red

I tried this query but failed to filter out the Rose and sky in the right table

SELECT T1.ccID, T0.objID, T0.objName, T1.Color
FROM tblObject T0
LEFT JOIN tblColorCombination T1 ON T1.objID = T0.objID AND T1.Color IN ('Red', 'Blue')

If I understand correctly, you want to get those rows only which occur with both colors, Red and blue and additionaly filter out such rows that occur with other colors, too. This can for example be done using a CTE like this:

WITH redBlue AS
(SELECT 
tcc.ccID, tbo.objID, tbo.objName, tcc.color,
COUNT(CASE WHEN tcc.color = 'Red' THEN 1 END) 
  OVER(PARTITION BY tcc.objID) AS countRed,
COUNT(CASE WHEN tcc.color = 'Blue' THEN 1 END) 
  OVER(PARTITION BY tcc.objID)  AS countBlue
FROM tblObject tbo
INNER JOIN tblColorCombination tcc
ON tbo.objID = tcc.objID
WHERE
  NOT EXISTS (SELECT 1 FROM tblColorCombination 
                WHERE objID = tcc.objID 
                  AND color NOT IN ('Red','Blue')))
SELECT ccId, objId, objName, color 
  FROM redBlue WHERE LEAST(countRed, countBlue) > 0;

The NOT EXISTS part filters out entries having other colors. The count conditions make sure to only get those entries which have both colors at least once.

This is the result created by the CTE query:

The complete query produces following result for your sample data:

Try out here: db<>fiddle

If it is intended to check an exact number of occurences rather than just both should be > 0, then the last part LEAST(countRed, countBlue) > 0 can be changed to something like countRed = 1 AND countBlue = 1.

Note: Depending on your exact requirements and data, the WHERE clause of the CTE query is not required and the result will still be the same without that part. See the sample fiddle. It show when this makes a difference and when not. It's up to you what exactly you need.

Please note that LEFT JOIN (and RIGHT JOIN for that matter) have specific meanings in SQL: they both refer to an OUTER JOIN and is not what you want here.

It was not clear from your question, if you are seeking those records from tblObject that have both red and blue and no other, or all records that have at least red and blue. If you want the latter, then the following approach is relatively simple to understand:

WITH cte AS
(
	SELECT ccRed.ccID AS redID, ccBlue.ccID as blueId, o.objName, 
	ccRed.Color as redColor, ccBlue.Color as blueColor 
	FROM tblObject o
	INNER JOIN tblColorCombination ccRed ON ccRed.objID = o.objID AND ccRed.Color = 'Red'
	INNER JOIN tblColorCombination ccBlue ON ccBlue.objID = o.objID AND ccBlue.Color = 'Blue'
)
SELECT redID as ccID, objName, redColor as Color
FROM cte
UNION
SELECT blueID as ccID, objName, blueColor as Color
FROM cte
ORDER BY 1;

By way of explanation, the CTE finds records that exist with both colours by means of two inner joins to the same table, each with a different extra characteristic - Color. The only records with red and blue are the only ones to meet the double join.

We then do a SELECT ... UNION SELECT ... to split the combined values in the CTE back to separate records.