I wrote the query below a while back to select all products which are associated with an outlet.
I now wish to add in another layer which only selects a product when product group is in table 'ProductGroupTerritories' (which has fields; Group_ID and Territory_ID)
If the group_id and territory of the outlet aren't in this table, I don't want it returned to the user.
Could anybody provide some logic assistance with this query? I'm really struggling today!!
Many thanks in advance,
c.ProductID AS Product_ID,
c.ProductName AS Product_Name,
ProductSuppliers.SupplierName AS Product_Supplier,
@Username as Username,
INNER JOIN ClientAccounts AS a ON AccountOutlets.Account_ID = a.Account_ID
INNER JOIN OutletList ON AccountOutlets.Outlet_ID = OutletList.Outlet_ID
RIGHT OUTER JOIN Products AS c
INNER JOIN ProductGroups pg ON pg.GroupID=c.Group_ID
INNER JOIN ProductSuppliers ON c.SupplierID = ProductSuppliers.SupplierID
LEFT OUTER JOIN AccountOutletProducts AS aocf ON c.ProductID = aocf.Product_ID AND aocf.Account_ID IN (SELECT Account_ID
FROM @OutletList) ON a.Account_ID = aocf.Account_ID
WHERE (AccountOutlets.Outlet_ID IN (Select Outlet_ID FROM @OutletList)