Joining over an interim table SQL Server

Question

I have a table of rules for pricing. I am retrieving the max discount for each ProductTypeID, which indicates which type a product is, using this query :

SELECT MAX(discount) as BiggestDiscount, ProductTypeID FROM dbo.SellingPriceRules
WHERE ProductTypeID is not null
GROUP by ProductTypeID
ORDER BY ProductTypeID

This works perfectly, however I need to expand on this and, for a list of ProductIDs retrieve my biggest discount. So I need to find what ProductTypeID each ProductID belongs to and check my SellPriceRules database for the max discount for this ProductTypeID.

So, in my Discounts table, I have :

ProductID, Margin

And in my Products Table I have :

ProductID, ProductTypeID

In order to get the ProductTypeID of each product, I have :

select * from Discounts m
INNER JOIN Product p on p.ProductID = m.ProductID
WHERE ProductTypeID is not null

I am now struggling with joining these two queries together. I simply want to get the max discount for each product in the discounts table and subtract this from my margin. How can I join these two retirevals together?

Thanks very much

Answer 1

You have all the logic correct. You just need the syntax of embedding one query inside another.

SELECT
  p.ProductID,
  p.ProductTypeID,
  m.Margin,
  d.BiggestDiscount,
  m.Margin - d.BiggestDiscount AS AdjustedMargin
FROM Product p
INNER JOIN Discounts m ON (p.ProductID = d.ProductID)
INNER JOIN (
  SELECT
    ProductTypeID,
    MAX(discount) as BiggestDiscount
  FROM SellingPriceRules
  GROUP BY ProductTypeID
) d ON (p.ProductTypeID = d.ProductTypeID)
WHERE p.ProductID IS NOT NULL