SQL Syntax for Complex Scenario (Deals)

Question

hello everyone

i have a complex query to be written but cannot figure it out

here are my tables

Sales --one row for each sale made in the system
SaleProducts --one row for each line in the invoice (similar to OrderDetails in NW)
Deals --a list of possible deals/offers that a sale may be entitled to
DealProducts --a list of quantities of products that must be purchased in order to get a deal

now im trying to make a query which will tell me for each sale which deals he may get

the relevant fields are:

Sales: SaleID (PK)
SaleProducts: SaleID (FK), ProductID (FK)
Deals: DealID (PK)
DealProducts: DealID(FK), ProductID(FK), Mandatories (int) for required qty

i believe that i should be able to use some sort of cross join or outer join, but it aint working

here is one sample (of about 30 things i tried)

SELECT  DealProducts.DealID, DealProducts.ProductID, DealProducts.Mandatories, 
        viwSaleProductCount.SaleID, viwSaleProductCount.ProductCount
FROM    DealProducts 
            LEFT OUTER JOIN viwSaleProductCount 
                ON DealProducts.ProductID = viwSaleProductCount.ProductID
GROUP BY DealProducts.DealID, DealProducts.ProductID, DealProducts.Mandatories, 
         viwSaleProductCount.SaleID, viwSaleProductCount.ProductCount

The problem is that it doesn't show any product deals that are not fulfilled (probably because of the ProductID join). i need that also sales that don't have the requirements show up, then I can filter out any SaleID that exists in this query where AmountBought < Mandatories etc

Thank you for your help

Answer 1

I'm not sure how well I follow your question (where does viwSaleProductCount fit in?) but it sounds like you will want an outer join to a subquery that returns a list of deals along with their associated products. I think it would go something like this:

Select *
From Sales s Inner Join SaleProducts sp on s.SaleID = sp.SaleID
    Left Join (
        Select *
        From Deals d Inner Join DealProducts dp on d.DealID = dp.DealId
    ) as sub on sp.ProductID = sub.ProductID

You may need to add logic to ensure that deals don't appear twice, and of course replace * with the specific column names you'd need in all cases.

edit: if you don't actually need any information from the sale or deal tables, something like this could be used:

Select sp.SaleID, sp.ProductID, sp.ProductCount, dp.DealID, dp.Mandatories
From SaleProducts sp
    Left Join DealProducts as dp on sp.ProductID = dp.ProductID

If you need to do grouping/aggregation on this result you will need to be careful to ensure that deals aren't counted multiple times for a given sale (Count Distinct may be appropriate, depending on your grouping). Because it is a Left Join, you don't need to worry about excluding sales that don't have a match in DealProducts.