Please see the picture for an overview of the small database I'm working with. The following SQL string will get me the order ID, customer name, salesperson name, and shipping method for a specified salesperson and shipping method.
SELECT OrderInfo.ID, CustomerInfo.Name, SalesPerson.Name, ShipperInfo.Name
FROM ShipperInfo INNER JOIN (SalesPerson INNER JOIN (CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.ID = OrderInfo.CustomerID) ON SalesPerson.ID = OrderInfo.SalesPersonID) ON ShipperInfo.ID = OrderInfo.ShipperID
WHERE SalesPerson.Name="Andrew Fuller" and shipperinfo.name = "United Package";
I need to be able to filter this data to only include orders which contained a specific product. For example, along with the other WHERE criteria only include orders where one of the items ordered was Boston Crab Meat (i.e. ProductInfo.Name = "Boston Crab Meat")
Thanks a million