Distinct, Group By
-
I have a products table that has many photos, the products table and the photos table are connected by a tween table.
I am trying to select only one photo per product.
The trouble that I am having is that if the product has three photos then that product is returned three different times.
I have attached the schema, and the results that I keep getting.
The schema
The Results
Thanks for the help !
Erik
Dam again!
-
you'll have to decide which photo to choose. for the example below, i chose the photo with the highest ID value.
select PR.*, PH.*
from utbProduct PR
join (select ProductId, max(PhotoId) as PhotoId from utbProductUtbPhoto
group by ProductId) as PRPH
on PR.ProductId = PRPH.ProductId
join utbPhoto PH
on PRPH.PhotoId = PH.PhotoId
if some products don't have photo's the JOINs will have to be changed to LEFT OUTER JOIN.
-
Absolutely beautiful my dear Watson !
SELECT
PR.[ProductID]
,PR.[SubCategoryID]
,PR.[ProductOptionID]
,PR.[ModelID]
,PR.[Name] AS 'ProductName'
,PR.[Number]
,PR.[DiscontinuedDate]
,PR.[Color]
,PR.[SafetyStockLevel]
,PR.[ReorderPoint]
,PR.[StandardCost]
,PR.[ListPrice]
,PR.[Size]
,PR.[WeightUnitMeasureCode]
,PR.[SizeUnitMeasureCode]
,PR.[Weight]
,PR.[DaysToManufacture]
,PR.[ProductLine]
,PR.[Style]
,PR.[SellStartDate]
,PR.[SellEndDate]
,PR.[ModifiedDate] AS 'ProductModifiedDate'
------------------------------
,ph.[PhotoID]
,ph.[Name] AS 'PhotoName'
,ph.[PhotoTypeID]
,ph.[ModifiedDate] AS 'PhotoModifiedDate'
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
WHERE pr.SubCategoryID = @SubCategoryID;
select * from fn_utbProduct_Select_W_Top1Photo(2)
Dam again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply