SQL composite key query

Question

to reference this again http://stackoverflow.com/questions/501347/sql-products-productsales

how could i do something like that when the primary key is made up of two columns and not one?

so products has two columns as PK, and productsales has two columns as FK.

here is the solution with a 1-column key:

SELECT p.[name]
FROM products p
WHERE p.product_id in (SELECT s.product_id
    FROM productsales s
    WHERE s.[date] between @dateStart and @dateEnd
    GROUP BY s.product_id
    HAVING Sum(s.quantity) > @X )

Answer 1

Something along these lines could work

SELECT p.[name]
FROM products p
JOIN (SELECT s.key1, s.key2
    FROM productsales s
    WHERE s.[date] between @dateStart and @dateEnd
    GROUP BY s.product_id
    HAVING Sum(s.quantity) > @X ) as a on a.key1 = p.key1 and a.key2 = p.key2

Answer 2

Maybe something like this would do:

SELECT p.first_col_id,p.second_col_id 
FROM products p
JOIN productsales s
   ON s.first_col_id = p.first_col_id 
  AND s.second_col_id = p.second_col_id 
  AND s.[date] between @dateStart and @dateEnd
GROUP BY p.first_col_id,p.second_col_id 
HAVING Sum(s.quantity) > @X )

Answer 3

Try:

SELECT p.[name]
FROM products p
WHERE (p.product_key1, p.product_key2) in
   (SELECT s.product_key1, s.product_key2
    FROM productsales s
    WHERE s.[date] between @dateStart and @dateEnd
    GROUP BY s.product_key1, s.product_key2
    HAVING Sum(s.quantity) > @X )

Answer 4

Refactored with one key

SELECT s.product_id, p.[name]
FROM products p JOIN productsales s ON p.product_id=s.product_id
WHERE s.[date] between @dateStart and @dateEnd
GROUP BY s.product_id, p.[name]
HAVING Sum(s.quantity) > @X )

Same on two keys:

SELECT s.k1, s.k2, p.[name]
FROM products p JOIN productsales s ON (p.k1=s.k1 AND p.k2=s. k2)
WHERE s.[date] between @dateStart and @dateEnd
GROUP BY s.k1, s.k2, p.[name]
HAVING Sum(s.quantity) > @X )

Answer 5

I like to do it like this:

;with T as( SELECT s.product_id
FROM productsales s
WHERE s.[date] between @dateStart and @dateEnd
GROUP BY s.product_id
HAVING Sum(s.quantity) > @X )

SELECT p.[name] FROM products p join T on p.PK1 = T.FK1 AND p.PK2=T.FK2

Answer 6

It looks like there are a few answers above that will work, but just to throw another solution at you in case it works better in your case:

SELECT
     P.name
FROM
     Products P
WHERE
     EXISTS
     (
          SELECT
               *
          FROM
               ProductSales PS
          WHERE
               PS.product_id = P.product_id AND
               PS.date BETWEEN @date_start AND @date_end
          GROUP BY
               PS.product_id
          HAVING
               SUM(PS.quantity) > @cutoff_quantity
     )

This method will tend to perform worse than the INNER JOIN with a GROUP BY method given by Mr. Brownstone, but in some situations depending on your value of @cutoff_quantity and table sizes it could perform better.