Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Record Being Duplicated In Join Statement Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 10:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 8:19 PM
Points: 5, Visits: 13
In the statement below, there is a problem where some records are occasionally duplicated. I have not been able to figure out where my problem is.

When I complete the query below, it returns the correct number of records without any duplicates.

SELECT        COUNT(*) AS Expr1
FROM salesExport AS SAL LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate

WHERE (SAL.businessDate = '7/10/2013')

When I add the EMP table, it duplicates two of my records.

SELECT        COUNT (*)
FROM employee AS EMP1 RIGHT OUTER JOIN
salesExport AS SAL ON EMP1.microsEmployeeNumber = SAL.microsEmployeeNumber LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate
WHERE SAL.businessDate = '7/10/2013'

I've tried a couple different approaches, but keep breaking something else...hopefully its just tired eyes. Any help is appreciated.

(BTW, the actual query is not a count statement, but this returns the same problem and is significantly shorter.)
Post #1475725
Posted Saturday, July 20, 2013 3:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:09 AM
Points: 1,916, Visits: 2,337
mkswanson (7/19/2013)
In the statement below, there is a problem where some records are occasionally duplicated. I have not been able to figure out where my problem is.

When I complete the query below, it returns the correct number of records without any duplicates.

SELECT        COUNT(*) AS Expr1
FROM salesExport AS SAL LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate

WHERE (SAL.businessDate = '7/10/2013')

When I add the EMP table, it duplicates two of my records.

SELECT        COUNT (*)
FROM employee AS EMP1 RIGHT OUTER JOIN
salesExport AS SAL ON EMP1.microsEmployeeNumber = SAL.microsEmployeeNumber LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate
WHERE SAL.businessDate = '7/10/2013'

I've tried a couple different approaches, but keep breaking something else...hopefully its just tired eyes. Any help is appreciated.

(BTW, the actual query is not a count statement, but this returns the same problem and is significantly shorter.)



can you please provide ddl statments with some sample data......
As per me I think as your using frist RIGHT OUTER JOIN THEN LEFT OUTER JOIN for salesexport table that is causing the duplicates values...
oNce you post sample data n table structure we will try to solve your porblem



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1475743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse