I have query below to determine everything in customers cart before preceeding to check-out. I've tried a few things and thought the best approach is to get everything in a single query, build a dataset, and then create filtered views of it as needed (ASP.NET).
The query works until I get a product with options and then it doesn't display the products with options. An earlier version displayed two for each one with options.
Please note the attached table structure.
Select OrderDetails.OrderDetailID, OrderDetails.ProductID, Products.ProductName, Products.ProductDescription,
OrderDetails.Quantity, Products.UnitPrice, OptionDetails.CostAdjust, OptionDetails.OptionDetailName, Options.OptionName
From OrderDetails Join Products On Products.ProductID = OrderDetails.ProductID
Left Join OrderOptionDetails On OrderDetails.OrderDetailID = OrderOptionDetails.OrderDetailID
Left Join OptionDetails On OptionDetails.OptionDetailID = OrderOptionDetails.OptionDetailID
Left Join Options On Options.OptionID = OptionDetails.OptionID
Where OrderDetails.OrderID = someValue;