SQL Syntax for Complex Scenario (Deals)
- by Yisman
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 doesnt show any product deals that r not fullfiled (probably because of the productid join). i need that also sales that dont have the requiremnets show up, then i can filter out any saleid that exists in this query "where AmountBought
thank you for your help
