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