I have the following Query:

SELECT 	a.cProduct_id,a.cProduct_Name,a.cStatus_id,
	(SELECT TOP 1 cItemCode 
	 FROM Product_Codes 
	 WHERE cCode1 = '10044444055555') AS cCode1, 
	(SELECT TOP 1 cItemCode 
	 FROM Product_Codes 
	 WHERE cCode2 = '10044444055555') AS cCode2, 
	(SELECT TOP 1 cItemCode 
	 FROM Product_Codes 
	 WHERE cCode3 = '10044444055555') AS cCode3 
FROM Product a
WHERE a.cStatus_id = 'RELE'

Table "Product_Codes" has index for fields "cCode1","cCode2", and "cCode3".
This works fine, the Query is quick (1 second), the 3 Queries on the Field list do an "Index Seek", so it seems fine.

Now the problem is that if I add to the "WHERE" clause to make it:

 "WHERE a.cStatus_id = 'RELE' AND a.iProduct_id = a.iProductGroup_id "

then the query instead of taking a second, takes over 3 minutes to run. I noticed that the last 2 queries (on "Product_Codes") are using the index of the field "cCode1" (which is clustered by the way) for some reason and doing a "Clustered index SCAN".

I tried to do the "WITH (index = indx_Code2)" and "WITH (index = indx_Code3)" for the last 2 queries to force them to use the right index and it does use them but does a "Index Scan" instead of the "Index Seek" that it uses when I don't modify the "WHERE" clause.

Any ideas why when modifying the "WHERE" clause it's not using the right index and if I force it then it's not using the "index seek"?

Thanks.