none
Identify Mismatch Records in a table

    질문

  • Hi,
        I want to find the mismatch records for the ID column from both the table
    Tran id exists in both the table but amount is mismatched

    CREATE TABLE TB1
    (
    TB1_ID INT,
    TB1_TRAN_DATE DATE 
    TB1_AMT NUMERIC(20,2)
    )

    INSERT INTO TB1 (1000001,'2018-04-18','100.00')
    INSERT INTO TB1 (1000002,'2018-04-18','101.00')
    INSERT INTO TB1 (1000003,'2018-04-18','102.00')


    CREATE TABLE TB2
    (
    TB2_ID INT,
    TB2_TRAN_DATE DATE 
    TB2_AMT NUMERIC(20,2)
    )

    INSERT INTO TB2 (1000001,'2018-04-18','100.00')
    INSERT INTO TB2 (1000002,'2018-04-18','103.00')
    INSERT INTO TB2 (1000003,'2018-04-18','102.00')


    select * from TB1

    Expected result

    TB1_ID            TB1_TRAN_DATE                     TB1_AMT

    1000002          2018-04-18                            101.00

    Please help

    2018년 5월 18일 금요일 오전 11:08

모든 응답

  • This should work:

    SELECT *
    FROM TB1
         INNER JOIN
         TB2
             ON TB1.TB1_ID = TB2.TB2_ID 
    WHERE TB1.TB1_AMT <> TB2.TB2_AMT


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    2018년 5월 18일 금요일 오전 11:21
  • SELECT
    TB1_ID,
    TB1_TRAN_DATE,
    TB1_AMT
    FROM
    TB1 A
    INNER JOIN
    TB2 B
    ON A.TB1_ID=B.TB2_ID
    AND A.TB1_TRAN_DATE=B.TB2_TRAN_DATE
    AND A.TB1_AMT<>B.TB2_AMT


    Saravanan ----------------------------------------------------------------------- Ever tried. Ever failed. No matter. Try Again. Fail again. Fail better.

    2018년 5월 18일 금요일 오전 11:26
  • Hi,

    this works also for null values:

    select TB1_ID, TB1_AMT from TB1 
    except
    select TB2_ID, TB2_AMT from TB2

    marc.

    2018년 5월 18일 금요일 오후 12:03
  • SELECT *
    FROM TB1 t
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM TB2
    WHERE TB2_ID = t.TB1_ID
    AND TB2_TRAN_DATE = t.TB1_TRAN_DATE
    AND TB2_AMT = t.TB1_AMT
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 1:46
  • Hi,

    Or you can try this:

    CREATE TABLE #TB1
     (
     TB1_ID INT,
     TB1_TRAN_DATE DATE, 
     TB1_AMT NUMERIC(20,2)
     )
    
     INSERT INTO #TB1 values (1000001,'2018-04-18','100.00')
     INSERT INTO #TB1 values (1000002,'2018-04-18','101.00')
     INSERT INTO #TB1 values (1000003,'2018-04-18','102.00')
    
    
     CREATE TABLE #TB2
     (
     TB2_ID INT,
     TB2_TRAN_DATE DATE,
     TB2_AMT NUMERIC(20,2)
     )
    
     INSERT INTO #TB2 values (1000001,'2018-04-18','100.00')
     INSERT INTO #TB2 values (1000002,'2018-04-18','103.00')
     INSERT INTO #TB2 values (1000003,'2018-04-18','102.00')
    
    
     select * from #TB1
     select * from #TB2
    
     select t1.*
     from #TB1 t1
     cross apply (select * from #TB2 t2 where t1.TB1_ID= t2.TB2_ID and t1.TB1_AMT<>t2.TB2_AMT) t

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 21일 월요일 오전 1:54