SQL composite key query

Question

to reference this again 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 )

Solution 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 

Solution 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 )

Solution 3

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.

Solution 4

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 )

Solution 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 

View more solutions