select on one result from the query_问答_开发者

I'm trying to query a few tables in our db that contains product data. It is a MSSQL 2005 database. The problem is its returning multiple rows for one product when I need it to only produce one row per product. Blow is the query and results I'm using.

SELECT      ProductItem.sku, ProductItem.title, ProductItem.short_desc, ProductItem.long_desc, ProductItem.active, ProductItem.product_item_id, 
                    ProductCategory.category_des开发者_开发问答c, ProductCategoryMap.product_id, ProductCategory.active AS activecat, Product.adwords_label, 
                    ProductItem.item_price, ProductItem.sale_price
FROM          Product INNER JOIN
                        ProductCategoryMap INNER JOIN
                        ProductCategory ON ProductCategoryMap.product_category_id = ProductCategory.product_category_id ON 
                        Product.product_id = ProductCategoryMap.product_id FULL OUTER JOIN
                        ProductItem ON Product.product_key = ProductItem.sku
WHERE      (ProductItem.active = 1) AND (ProductCategory.active = 1)

This returns the following results:

select on one result from the query

I know the problem occurs because the product resides in multiple categories, but really I don't need every category its in, just one of them. So ideally just one product for every row returned

I can't figure out how to make my query achieve that though.

Can anyone help me out please?

At first glance, take category columns out and add DISTINCT. You've asked for "category" so you can get all categories.

Also:

the WHERE changes your FULL OUTER JOIN to an INNER
get product_id from Product
the activecat column is implied by the where clause anyway

I've tweaked it for clarity and added an aggregate to get one category

SELECT
   PI.sku, PI.title, PI.short_desc, PI.long_desc, 
   PI.active, PI.product_item_id, 
   PI.item_price, PI.sale_price, 

   MIN(PC.category_desc),
   P.product_id,
   1 AS activecat, --implied by filter

   P.adwords_label
FROM 
    ProductItem PI
    INNER JOIN
    Product P ON P.product_key = PI.sku
    INNER JOIN
    ProductCategoryMap PCM ON P.product_id = PCM.product_id
    INNER JOIN
    ProductCategory PC ON PCM.product_category_id = PC.product_category_id
WHERE
   (PI.active = 1) AND (PC.active = 1)
GROUP BY
   PI.sku, PI.title, PI.short_desc, PI.long_desc, 
   PI.active, PI.product_item_id, 
   PI.item_price, PI.sale_price, 
   P.product_id,
   P.adwords_label

Edit: you can tidy it more with APPLY which will also deal with no categories if changed to OUTER APPLY

SELECT
   PI.sku, PI.title, PI.short_desc, PI.long_desc, 
   PI.active, PI.product_item_id, 
   PI.item_price, PI.sale_price, 

   PC2.category_desc,
   P.product_id,
   PC2.active AS activecat,

   P.adwords_label
FROM 
    ProductItem PI
    INNER JOIN
    Product P ON P.product_key = PI.sku
    CROSS APPLY
    (
    SELECT TOP 1
        PC.category_desc, PC.active
    FROM
        ProductCategoryMap PCM
        INNER JOIN
        ProductCategory PC ON PCM.product_category_id = PC.product_category_id
    WHERE
       P.product_id = PCM.product_id AND PC.active = 1
    ORDER BY 
       PC.category_desc
    ) PC2
WHERE
   PI.active = 1

The answer depends a bit upon what you want.

If you only care about product category for the sake of asking "does this product have at least 1 active category?" Your query would look like ...

SELECT      DISTINCT
            ProductItem.sku, 
            ProductItem.title, 
            ProductItem.short_desc, 
            ProductItem.long_desc, 
            ProductItem.active, 
            ProductItem.product_item_id, 
            ProductCategoryMap.product_id, 
            Product.adwords_label, 
            ProductItem.item_price, 
            ProductItem.sale_price
FROM      
            Product 
INNER JOIN  ProductCategoryMap   ON Product.product_id = ProductCategoryMap.product_id 
INNER JOIN  ProductCategory     ON ProductCategoryMap.product_category_id = ProductCategory.product_category_id 
FULL JOIN   ProductItem         ON Product.product_key = ProductItem.sku
WHERE      
            (ProductItem.active = 1) AND 
            (ProductCategory.active = 1)

If you don't care about category at all it would look like ....

SELECT      
            ProductItem.sku, 
            ProductItem.title, 
            ProductItem.short_desc, 
            ProductItem.long_desc, 
            ProductItem.active, 
            ProductItem.product_item_id, 
            ProductCategoryMap.product_id, 
            Product.adwords_label, 
            ProductItem.item_price, 
            ProductItem.sale_price
FROM      
            Product 
INNER JOIN  ProductCategoryMap  ON Product.product_id = ProductCategoryMap.product_id 
ProductCategory.product_category_id 
FULL JOIN   ProductItem         ON Product.product_key = ProductItem.sku
WHERE      
            (ProductItem.active = 1)

If you want to include active category names as a CSV list (just mind the execution plan if this is a large result set) ....

SELECT      ProductItem.sku, 
            ProductItem.title, 
            ProductItem.short_desc, 
            ProductItem.long_desc, 
            ProductItem.active, 
            ProductItem.product_item_id, 
            ProductCategory.category_desc, 
            ProductCategoryMap.product_id, 
            ProductCategory.active AS activecat, 
            Product.adwords_label, 
            ProductItem.item_price, 
            ProductItem.sale_price,
            (SELECT substring((SELECT ( ', ' + CAST(PC.category_desc AS varchar(50)))
                                FROM    ProductCategory PC
                                WHERE   PC.product_category_id =  ProductCategoryMap.product_category_id AND
                                        PC.active = 1
                                FOR XML PATH( '' )
                              ), 3, 8000 )  
                   )      AS    ProductCategoriesCSV
FROM      
            Product 
INNER JOIN  ProductCategoryMap  ON Product.product_id = ProductCategoryMap.product_id 
INNER JOIN  ProductCategory     ON ProductCategoryMap.product_category_id = ProductCategory.product_category_id 
FULL JOIN   ProductItem         ON Product.product_key = ProductItem.sku
WHERE      
            (ProductItem.active = 1)

HTH, -eric

Try...

SELECT      
  I.sku, 
  I.title, 
  I.short_desc, 
  I.long_desc, 
  I.active, 
  I.product_item_id, 
  T.category_desc, 
  P.product_id, 
  T.active AS activecat, 
  P.adwords_label, 
  I.item_price, 
  I.sale_price
FROM          
  Product P

  CROSS APPLY
  (
    SELECT TOP 1
      C.category_desc,
      C.active AS activecat
    FROM
      ProductCategoryMap CM 

      INNER JOIN ProductCategory C
      ON CM.product_category_id = C.product_category_id 
    WHERE
      P.product_id = CM.product_id
      AND 
     (C.active = 1)
  ) T

  FULL OUTER JOIN ProductItem I
  ON P.product_key = I.sku
WHERE 
 (I.active = 1)