Record Being Duplicated In Join Statement

  • 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.)

  • 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/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply