SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    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.

    Code:
    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;
    Attached Images Attached Images
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •