Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Duplicate rows returned from join

Author  Topic 

Angate
Starting Member

24 Posts

Posted - 2010-06-27 : 18:01:15
When I run the following statement, I get duplicate rows returned. I do not get duplicates when I remove the Left Join to parentChildTable. I suspect that the issue is the type of join I am using, but am unsure. Ideas?

SELECT SUM(CT.quantity * (PT.productPrice - (CASE WHEN CT.asChildOf IS NULL THEN 0 ELSE PCT.asChildDiscount END)))
FROM cartTable AS CT
INNER JOIN productTable AS PT
ON PT.productID = CT.productID
LEFT JOIN parentChildTable AS PCT
ON CT.productID = PCT.childID
WHERE CT.customerNumber = @customerNumber


Situation Description:

This query sums the customer's cart selections. The customer can select options for their product selections, like getting discounts for selecting accessories for a power tool when you buy it. The issue is the duplicates are coming from the join to the parentChildTable, that stores the relationship between products for them being parent/child, and returns them being discounted for being selected together.


Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 03:09:31
You need to post some sample data.
My wildest guess would be add a group by clause for PCT.childID in the query.



Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH
Go to Top of Page

Angate
Starting Member

24 Posts

Posted - 2010-06-28 : 15:56:37
I apologize for not providing sample data. One of my biggest concerns with grouping is that it is possible for a customer to have two duplicate items with duplicate selections in their cart. The unique identifiers for the line items is the CT.cartItemID.

Here is the query I used to find out that there were in fact duplicates.


SELECT CT.cartItemID, PT.productID, CT.quantity, PT.productPrice, CT.asChildOf, PCT.asChildDiscount
FROM cartTable AS CT
LEFT JOIN productTable AS PT
ON PT.productID = CT.productID
LEFT JOIN parentChildTable AS PCT
ON CT.productID = PCT.childID
WHERE customerNumber = 1 AND CT.quantity > 0 –---the options that are not selected are still added to cart with quantity 0 for place holder reasons


It returns this:




This is what is actually stored in the cart:

Go to Top of Page

Angate
Starting Member

24 Posts

Posted - 2010-06-30 : 09:53:37
I have added more test data to the database and have narrowed down the issue. What is happening is that a 'child' product listed in the parentChildDiscount is being added to the sum for each entry in the PCT. What I need to do is find a way to include a WHERE criteria that makes sure that the child's parent is it's 'asChildOf' in the cartTable. The thing that has me stumped is that 'asChildOf' stores the child's parent's cartID for the sake of unique identification, not it's productID. Any idea about how I might get this sorted out?

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 10:18:23
Think about it logically

You have a Parent and you join to car table and you get 2 rows

1 for the parent, 2 for the cart

Parent 1, Cart 1
Parent 1, Cart 2

Now you want to add something that is NOT Related to Cart, except viw the Parent...(in the ParentChild Table..we'll call PC)

If the Parent Join ONLY to PC is

Parent 1, PC X
Parent 1, PC Y

Now you want to put all of this together..ok...let's go back to Cart row 1, now adding PC. You need to account for everuthing for Cart 1, and let's say we only want WHERE Cart = 1...you MUST get

Parent 1, Cart 1, PC X
Parent 1, Cart 1, PC Y

But if we remove that, the you MUST get

Parent 1, Cart 1, PC X
Parent 1, Cart 1, PC Y
Parent 1, Cart 2, PC X
Parent 1, Cart 2, PC Y

Make sense??????









Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Angate
Starting Member

24 Posts

Posted - 2010-06-30 : 15:56:30
I worked though that a few times and was unable to make sense of it. I am filtering by customerNumber, but the duplicates are coming from the rows in the parentChildTable, one for each entry for a particular item to be a child. I see why it is returning what it is, I just don't know how to filter it without something like DISTINCT, or storing the parent's actual productID in the cart table.
Go to Top of Page

CBrammer
Starting Member

7 Posts

Posted - 2012-07-25 : 10:36:46
Here is my sample code, When I JOIN the tables that are commented out the query will return 1000 row for a personID this is because in one of the tables there is 1000 rows with that same personID with an attribute with a different value, they are sort of using it as a code(look up) table. I know there is a way to put those values in the same row as that one personID, but not sure how to make it just one row, any suggestions or help would be greatly appreciated.

USE [OPS]
DECLARE @endYear VARCHAR(MAX);
DECLARE @schoolID VARCHAR(MAX);
--DECLARE @grade VARCHAR(MAX);
--DECLARE @calendarID VARCHAR(MAX)

SET @endYear = '2012'
SET @schoolID = '2,3,4,5,6,7,8,9,12,13,14,15,16,17,18,19,23,25,26,27,28,29,32,33,34,35,36,37,38,40,42,46,47,48,49,51,52'
--SET @grade = '3,UN,12,HS,RU,03,09,NULL,2,6,06,PK,11,1,7,01,10,07,04,KG,5,05,02,4,08'
--SET @calendarID = ''

SELECT DISTINCT (person.personID)
,person.stateID
,enroll.endYear
--,custstud.attributeID
--,custstud.value
,enroll.active
,enroll.enrollmentID
,enroll.calendarID
,enroll.grade
--,ROW_NUMBER() OVER (PARTITION BY person.personID ORDER BY enroll.startDate DESC) AS rownum
,sch.[type] AS schoolnum
,sch.name AS schoolname
,cal.districtID
--,v_guard.guardian
--,addr.number
--,addr.street
--,addr.city
--,addr.state
--,addr.zip



--,custstud.value
--,v_sectinfo.teacherDisplay
--,v_sectinfo.teacherFirstName
--,v_sectinfo.teacherLastName





FROM dbo.Enrollment enroll
--will need a sub query to put contact ppl all in one row instead of multiple
--times for the same personID
INNER JOIN dbo.Person person
ON enroll.personID = person.personID
INNER JOIN dbo.Calendar cal
ON enroll.calendarID = cal.calendarID
INNER JOIN dbo.School sch
ON cal.schoolID = sch.schoolID
--INNER JOIN dbo.view_guardian v_guard
--ON v_guard.personID = person.personID
INNER JOIN dbo.householdmember hhm
ON person.personID = hhm.personID
INNER JOIN dbo.Household hh
ON hhm.householdID = hh.householdID
INNER JOIN HouseholdLocation hhl
on hh.householdID = hhl.householdID
--INNER JOIN dbo.Address addr
--ON hhl.addressID = addr.addressID
--INNER JOIN dbo.CustomStudent custstud
--ON person.personID = custstud.personID
--INNER JOIN dbo.v_SectionInfo v_sectinfo
--ON v_sectinfo.calendarID = enroll.calendarID

WHERE cal.schoolID IN (select * from list_to_tbl(@schoolID))
AND enroll.endYear = @endYear
AND enroll.active = 1
ORDER BY person.personID
Go to Top of Page
   

- Advertisement -