Hi
if you want show one row for one product( a product with a image) you must determine which of images is your favorite image fore select.
I think bellow code can help you:
select Product.ProductID As ID,
Product.ProductName As Name,
Product.ProductType As Type,
Product.SalesPrice As Price,
Product.DiscountPrice As Discount,
Product.SubCategoryID As SubCategory,
Product.BrandName As Branch,
Descriptions.Title As Description,
ISNULL((Select Top(1) ImageIcon
From ImageContainer
Where ProductID = Product.ProductID --And
-- Your Policy for select On image
-- Order By YourSelectPolicyColumnName Desc
) , 0) As Icon,
ISNULL((Select Top(1) SmallImage
From ImageContainer
Where ProductID = Product.ProductID --And
-- Your Policy for select On image
-- Order By YourSelectPolicyColumnName Desc
) , 0) As Image,
ISNULL((Select Top(1) MaxImage
From ImageContainer
Where ProductID = Product.ProductID --And
-- Your Policy for select On image
-- Order By YourSelectPolicyColumnName Desc
) , 0) As MaxImage,
from ProductMaster As Product
Inner Join
ProductDescription As Descriptions
On Product.ProductID = Descriptions.ProductID