I tried your code but I had to modify it b/c it needs to match to the 3 fields: cCode1,cCode2,cCode3 in 3 different ways, so modified it to:

SELECT 	cProduct_id, cProduct_Name, cStatus_id,
	(SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode1 = p.cCode1 ) AS cItemCode1,
	(SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode2 = p.cCode2 ) AS cItemCode2,
	(SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode3 = p.cCode3 ) AS cItemCode3
FROM (	SELECT 	cProduct_id, cProduct_Name, cStatus_id,
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Mfg AS varchar(7)),7) + 		
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Item AS varchar(7)),7) + 		
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Case AS varchar(7)),7) AS cCode1,
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Mfg AS varchar(7)),7) + 		
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Item AS varchar(7)),7) AS cCode2,
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Mfg AS varchar(7)),7) + 		
		RIGHT(REPLICATE('0',7) + CAST(iUPC_Case AS varchar(7)),7) AS cCode3
		FROM Product 
	WHERE cStatus_id = 'RELE' AND iProduct_id = iProductGroup_id) P

Now there's good news and bad news:

1) good news is that it's faster than before

2) the bad news is that is still slow,
- now it takes 20 seconds

3) if I take out the

"AND iProduct_id = iProductGroup_id"

from the WHERE clause, it takes 5 seconds

4) if I take out the

"AND iProduct_id = iProductGroup_id"

AND include the indexes for cCode1 and cCode2

"FROM Product_Codes WITH (index = indx_Code2)"

and

"FROM Product_Codes WITH (index = indx_Code3)

Then it's one second which is my goal, BUT of course if I add to the WHERE clause it takes 20+ seconds.

Still confused why adding the code to the WHERE clause is causing it to go really slow.