0

I have the query below, TBL1 X is my source table and TBL2 Y is my target table. I want to get only the data from TBL1 X that any of the fields indicated below don't match their corresponding fields in TBL2 Y. I am not a 100% sure that the code is correct. Can someone help please

SELECT
Col1,
Col2,
PRESC_ID,
PRSC_NPI_N,
FROM   TBL1 X
JOIN
(
SELECT
CLAIM_ID,
ColA,
ColB,
ColC,
ColD
FROM   TBL2 Y
) 
ON X.PHARM_ID = Y.CLAIM_ID

---- If this condition X.PHARM_ID= Y.CLAIM_ID and any of the ones below is satisfied, I want the record to be return.

WHERE  X.PHARM_ID= Y.CLAIM_ID
OR X.Col1 <> Y.ColA
OR X.Col2 <> Y.ColB
OR X.PRESC_ID <> Y.ColC
OR X.PRSC_NPI_N <> Y.ColD;
Bond
  • 25
  • 2
  • 13

2 Answers2

0

You're close:

SELECT
Col1,
Col2,
PRESC_ID,
PRSC_NPI_N,
FROM TBL1 X
JOIN TBL2 AS Y
ON X.PHARM_ID = Y.CLAIM_ID
WHERE X.Col1 <> Y.ColA
   OR X.Col2 <> Y.ColB
   OR X.PRESC_ID <> Y.ColC
   OR X.PRSC_NPI_N <> Y.ColD;

But I would prefer a Correlated Subquery:

SELECT
  Col1,
  Col2,
  PRESC_ID,
  PRSC_NPI_N,
FROM TBL1 X
WHERE EXISTS
 ( SELECT *
   FROM TBL2 AS Y
   WHERE X.PHARM_ID = Y.CLAIM_ID -- same ID
     AND           -- any other column is different
      (   X.Col1 <> Y.ColA
       OR X.Col2 <> Y.ColB
       OR X.PRESC_ID <> Y.ColC
       OR X.PRSC_NPI_N <> Y.ColD
      )
 );

Both versions will fail if columns contain NULLs.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
0

I think this is the correct logic, based on your description:

SELECT
  Col1,Col2,PRESC_ID,PRSC_NPI_N
FROM   TBL1 X
WHERE PHARM_ID in ( 
  SELECT CLAIM_ID FROM TBL2 Y
  where X.PHARM_ID= Y.CLAIM_ID
    AND (
      X.Col1 <> Y.ColA
      OR X.Col2 <> Y.ColB
      OR X.PRESC_ID <> Y.ColC
      OR X.PRSC_NPI_N <> Y.ColD
    )
)
access_granted
  • 1,807
  • 20
  • 25