Right now the query below is retrieving more then one product record. How can I limit the results set to only retrieving the one record per product_ID? Multiple records will be returned from different products, but I only need one product line per product_id. This is MS SQL 2005
SELECT DISTINCT dbo.Products.Product_ID AS Expr1,
CASE
WHEN dbo.Products.thumbnail IS NULL
OR dbo.Products.thumbnail = ''
THEN dbo.Products.Smimage
ELSE dbo.Products.thumbnail
END AS image ,
dbo.Products.ProductTitle ,
'<img WIDTH="62" src="http://media.companyinc.com/companyinc/SKUimages/small/' + dbo.Products.Smimage + '">' AS URLImage,
dbo.INV_dropshippers_To_ProductOptions.sku ,
dbo.Products.Discontinued ,
dbo.Products.CloseOut ,
dbo.Products.Special ,
dbo.Products.Active ,
dbo.Products.location_id ,
dbo.INV_dropshippers_To_ProductOptions.dropshipper_id ,
dbo.Products.season ,
dbo.Products.thumbnail ,
dbo.Products.Smimage ,
dbo.Products.CustomField2 ,
dbo.Products.pt_type ,
dbo.Products.PartNumber ,
dbo.Products.RetailPrice AS Price ,
dbo.skupurchasedreport.Product_Id ,
dbo.skupurchasedreport.totalprice_date1 ,
dbo.skupurchasedreport.totalprice_date2 ,
dbo.skupurchasedreport.qtypurchased_date1 ,
dbo.skupurchasedreport.qtypurchased_date2 ,
dbo.skupurchasedreport.totalprice_date3 ,
dbo.skupurchasedreport.qtypurchased_date3 ,
dbo.INV_dropshippers_To_ProductOptions.quantity AS currentqty
FROM dbo.Products
INNER JOIN dbo.skupurchasedreport
ON dbo.Products.Product_ID = dbo.skupurchasedreport.Product_Id
LEFT OUTER JOIN dbo.Options AS Options_2
RIGHT OUTER JOIN dbo.INV_dropshippers_To_ProductOptions
ON Options_2.Opt_ID = dbo.INV_dropshippers_To_ProductOptions.option_id3
LEFT OUTER JOIN dbo.Options AS Options_1
ON dbo.INV_dropshippers_To_ProductOptions.option_id2 = Options_1.Opt_ID
LEFT OUTER JOIN dbo.Options
ON dbo.INV_dropshippers_To_ProductOptions.option_id1 = dbo.Options.Opt_ID
ON dbo.Products.Product_ID = dbo.INV_dropshippers_To_ProductOptions.product_id
GROUP BY dbo.skupurchasedreport.Product_Id ,
dbo.INV_dropshippers_To_ProductOptions.product_id ,
dbo.Products.Product_ID ,
dbo.Products.ProductTitle ,
dbo.Products.thumbnail ,
dbo.Products.Smimage ,
dbo.INV_dropshippers_To_ProductOptions.sku ,
dbo.Products.Discontinued ,
dbo.Products.CloseOut ,
dbo.Products.Special ,
dbo.Products.Active ,
dbo.Products.location_id ,
dbo.INV_dropshippers_To_ProductOptions.dropshipper_id,
dbo.Products.season ,
dbo.Products.CustomField2 ,
dbo.Products.pt_type ,
dbo.Products.PartNumber ,
dbo.Products.RetailPrice ,
dbo.skupurchasedreport.Product_Id ,
dbo.skupurchasedreport.totalprice_date1 ,
dbo.skupurchasedreport.totalprice_date2 ,
dbo.skupurchasedreport.totalprice_date1 ,
dbo.skupurchasedreport.totalprice_date3 ,
dbo.skupurchasedreport.qtypurchased_date1 ,
dbo.skupurchasedreport.qtypurchased_date2 ,
dbo.skupurchasedreport.qtypurchased_date3 ,
dbo.INV_dropshippers_To_ProductOptions.quantity
Best Answer
Wow do you really want to group by that many fields? Generally when I see SQL statements that have some sort of aggregate and group by almost every column in the table it is a sign that it's probably wrong. It may return the right data for today but tomorrow's a different story. Read up on DISTINCT, Group By, and Inner Queries to handle this appropriately. The proper way to post this question was to give us some data from the table and give us what you want as expected output or desired results. My inclination tells me you want some sort of