Duplicates in Select Statement

AndyJay · #1 (**permalink**) 12-09-11, 09:43

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

r937 · #2 (**permalink**) 12-09-11, 10:04

Quote:

Originally Posted by AndyJay

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

AndyJay · #3 (**permalink**) 12-12-11, 05:28

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

r937 · #4 (**permalink**) 12-12-11, 05:35

could you post your query please (not an image, but the actual code)

AndyJay · #5 (**permalink**) 12-12-11, 05:43

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
%>

r937 · #6 (**permalink**) 12-12-11, 06:52

try LEFT OUTER JOIN instead of INNER JOIN

AndyJay · #7 (**permalink**) 12-12-11, 07:38

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

Internet Services

Web Development

Digital Marketing

Consumer Tech