Hi Roy,

It's not easy to provide help when you keep changing your story. Anyway, try

SELECT 	cProduct_id, cProduct_Name, cStatus_id,
	(SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode1 = p.cCode ) AS cItemCode1,
	(SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode2 = p.cCode ) AS cItemCode2,
	(SELECT TOP 1 cItemCode FROM Product_Codes WHERE cCode3 = p.cCode ) 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) AS cCode
			FROM Product 
		WHERE cStatus_id = 'RELE' AND iProduct_id = iProductGroup_id 
	) p

>I'm not sure I understand your question but basically if the "Product" table has 10 records I want the result to be 10 records, but the join can have multiple matches (on any of the 3 joins) so the result can be 10+, that's why I avoided the joins.
>
>I have to make a correction, the "WHERE cCode1", "WHERE cCode2", "WHERE cCode3", it's not using a fixed number it's actually some field manipulation, I put a fixed number to not make it confusing but now I think that's part of the issue. We can analyze the actual "SELECT" statement that's matching to "cCode2":
>
>

>(SELECT TOP 1 cItemCode
> FROM Product_Codes
> WHERE cCode2 = REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
>	        REPLICATE('0',7-LEN(RTRIM(CAST(a.iUPC_Item AS CHAR(7)))))+RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,
>

>
>
>Basically it's grabbing 2 fields from "Products" and making sure they're length = 7 and padding "0"s at the beginning if needed.
>
>So I tested using the hard coded numbers as in the original example and it came in one second even with the "AND a.iProduct_id = a.iProductGroup_id" added to the WHERE clause, all "index seeks", so worked good.
>
>so then I tested with taking out the REPLICATE commands in the current example (still with the "AND a.iProduct_id = a.iProductGroup_id"):
>
>

>(SELECT TOP 1 cItemCode
> FROM Product_Codes
> WHERE cCode2 = RTRIM(CAST(a.iUPC_Mfg AS CHAR(7)))+
>	        RTRIM(CAST(a.iUPC_Item AS CHAR(7)))) AS cCode2,
>

>
>and worked fine, one second and all "index" seeks. Of course if I added the REPLICATES it will crawl back to 3 minutes UNLESS it's the first SELECT for cCode1 which has a clustered index on that field in the "Product_Codes" Table, doing a "Clustered index seek".
>
>So basically I have narrowed it down to the REPLICATE command affecting the performance, but at the same time if I take out "AND a.iProduct_id = a.iProductGroup_id" the REPLICATE has no negative effect, it's fast.