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.
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 = @customerNumberSituation 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 |
|
|
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: |
|
|
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? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-06-30 : 10:18:23
|
Think about it logicallyYou have a Parent and you join to car table and you get 2 rows1 for the parent, 2 for the cartParent 1, Cart 1Parent 1, Cart 2Now 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 isParent 1, PC XParent 1, PC YNow 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 getParent 1, Cart 1, PC XParent 1, Cart 1, PC YBut if we remove that, the you MUST getParent 1, Cart 1, PC XParent 1, Cart 1, PC YParent 1, Cart 2, PC XParent 1, Cart 2, PC YMake sense??????Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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. |
|
|
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 personIDINNER JOIN dbo.Person personON enroll.personID = person.personIDINNER JOIN dbo.Calendar calON enroll.calendarID = cal.calendarIDINNER JOIN dbo.School schON cal.schoolID = sch.schoolID--INNER JOIN dbo.view_guardian v_guard--ON v_guard.personID = person.personIDINNER JOIN dbo.householdmember hhmON person.personID = hhm.personIDINNER JOIN dbo.Household hhON hhm.householdID = hh.householdIDINNER JOIN HouseholdLocation hhlon 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.calendarIDWHERE cal.schoolID IN (select * from list_to_tbl(@schoolID)) AND enroll.endYear = @endYear AND enroll.active = 1 ORDER BY person.personID |
|
|
|
|
|
|
|