Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Duplicates in Select Statement

    Can someone help with the logic of this one please?
    I need to create my select statement to pull product details based on category, clientid (database runs two e-commerce sites).
    When i add the 'ClientOffers' table, it messes up the results. i get some duplicates.
    Please see attached screen grabs.
    Andy
    Attached Thumbnails Attached Thumbnails view_1.jpg   view_2.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    Can someone help with the logic of this one please?
    i was going to ask what the heck a row is doing in the clientproducts table with a clientid of zero, but decided that i'd rather not

    let's just look at the cardinality of your joins

    we'll start with the WHERE clause, and discover that you're looking for all products in category 15

    so, since each product belongs to only one category, this join is okay

    (aside: since you are looking for all products in category 15, i would write the FROM clause starting with the categories table, then join to the products table, because this represents the "intent" of the query, so it's more logical, and you did want comments on your logic, right? but as these are all inner joins, this is really just a question of style, and although style is very important, it doesn't affect this particular problem)

    then you have the join from products to clientproducts

    based on your keys, a product can belong to more than client, but you have this restriction for "client zero" so once again this join is okay, as it will return only one row per product

    but when you add the clientoffers table, it "messes up the results"

    this is because a product can belong to multiple offers by multiple clients

    however, in your join, you have not ensured that you're bringing in only the offers for "client zero" and this is my guess as to the reason for the mess

    add the following condition to the join --
    Code:
    AND dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Thank You r937

    I have added that bit, but now im only getting records where the product has an offer!
    Category 15 should display 38 records and only 18 are being returned (the ones with an offer). it's as if it's filtering on the offers as well.
    I can't see the woods for the trees :-)

    Can you advise please?

    Thanks again
    Andy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you post your query please (not an image, but the actual code)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    Hi again r937

    Here is my code - thanks again for your time :-)
    Andy

    Code:
    <%
    Dim RSResults__param1
    RSResults__param1 = "0"
    If (Request("CategoryID") <> "") Then 
      RSResults__param1 = Request("CategoryID")
    End If
    %>
    <%
    Dim RSResults__param2
    RSResults__param2 = "-1"
    If (Application("ClientID") <> "") Then 
      RSResults__param2 = Application("ClientID")
    End If
    %>
    <%
    
    Set RSResults_cmd = Server.CreateObject ("ADODB.Command")
    RSResults_cmd.ActiveConnection = myconn_STRING
    RSResults_cmd.CommandText = "SELECT dbo.Categories.Category, dbo.ClientProducts.ClientID, dbo.Products.ProductID, dbo.Products.ManufacturerID, dbo.Products.Product, dbo.Products.Image, dbo.Products.Price, dbo.Categories.CategoryID, dbo.ClientOffers.OfferID FROM dbo.Products INNER JOIN dbo.ClientProducts ON dbo.Products.ProductID = dbo.ClientProducts.ProductID INNER JOIN dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID INNER JOIN dbo.ClientOffers ON dbo.Products.ProductID = dbo.ClientOffers.ProductID AND dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID WHERE (Categories.CategoryID = ?) AND (ClientProducts.ClientID = ?) AND (dbo.ClientOffers.ClientID = 0)"
    searchtype="CategoryID"
    RSResults_cmd.Prepared = true
    RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param1", 5, 1, -1, RSResults__param1) ' adDouble
    RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param2", 5, 1, -1, RSResults__param2) ' adDouble
    
    Set RSResults = RSResults_cmd.Execute
    RSResults_numRows = 0
    %>

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try LEFT OUTER JOIN instead of INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2008
    Posts
    120
    You, my friend, are a genius :-)
    That works perfectly now. Fantastic!

    My Simply SQL order will be placed today.............some reading up for me i think.

    Thanks again for your support and time.

    Andy

Posting Permissions

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