Results 1 to 2 of 2
Feb 1, 2005, 13:49 #1
- Join Date
- Mar 2000
- Muskegon, MI
- 0 Post(s)
- 0 Thread(s)
Complicated Query - Too Complicated?
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;Westmich
Smart Web Solutions for Smart Clients
Feb 1, 2005, 14:37 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 49 Post(s)
- 1 Thread(s)
it's probably your data
notice in your diagram how OrderDetails is not connected to Product
yet you are joining thos two in the first join
if the data is correct, the join will work, but since there is no pk/fk relationship between these two tables, whether the data is right is not controlled by the database