Hey GrassHopper I finally got it.

I had to think backwards.

ALTER proc [dbo].[uspUtbProduct_Select_W_Top1Photo_4Opts]

@SubCategoryID INT,

@ProductID INT

AS

DECLARE @OptionsTable TABLE

(

ProductID INT,

SubCategoryID INT,

ProductName VARCHAR(30),

ProductNumber VARCHAR(30),

PhotoID INT,

PhotoName VARCHAR(30)

)

INSERT INTO @OptionsTable

SELECT pr.ProductID,

------------------------------

-- prPo.ProductOptionID,

------------------------------

pr.SubCategoryID,

pr.Name AS 'ProductName',

pr.Number AS 'ProductNumber'

------------------------------

,ph.[PhotoID]

,ph.[Name] AS 'PhotoName'

FROM Production.utbProduct pr WITH(NOLOCK)

left outer join (select ProductId, max(PhotoId) as PhotoId

from Production.utbProductUtbPhoto WITH(NOLOCK)

group by ProductId) as prph

on pr.ProductId = prph.ProductId

left outer join Files.utbPhoto ph

on prph.PhotoId = ph.PhotoId

left join Production.utbProductUtbProductOption prpo

on prpo.ProductID = pr.ProductID

WHERE pr.SubCategoryID = @SubCategoryID

AND pr.ProductID <> @ProductID

AND pr.ProductID NOT IN (SELECT ProductOptionID FROM Production.utbProductUtbProductOption

WHERE ProductID = @ProductID)

SELECT * FROM @OptionsTable t1

exec [dbo].[uspUtbProduct_Select_W_Top1Photo_4Opts]2,7