How do I compare two columns from two different tables

Question

I am running a CREATE TABLE TBL AS SELECT statement as below. I want to write a CASE STATEMENT that will compare values from column X.PRESC_ID to values from column Y.PRSC_NPI and if there is match, it should INSERT to TBL.PRESC_ID, and for all the X.PRESC_ID that do not match with any value in Y.PRSC_NPI should be INSERTED to TBL.PRSC_NPI_N

CREATE  TABLE TBL (
      Col1,
      Col2,
      PRESC_ID,
      PRSC_NPI_N,
 AS 
 (
          SELECT  
                 Col1,
                 Col2,
                 PRESC_ID,
                 PRSC_NPI_N,
                 FROM   TBL2 X
    JOIN
        (SELECT CLAIM_ID,PRSC_NPI FROM TBL3) Y
                ON Y.CLAIM_ID  = Y.Col1

I have tried the one below but it is not working

CASE
WHEN X.PRESC_ID = Y.PRSC_NPI THEN TBL.PRESC_ID
ELSE TBL.PRSC_NPI_N
END

score 0 · Accepted Answer · answered Aug 08 '20 at 00:03

0

Seems you really want two CASE expressions, one for each result column. Something like

CASE WHEN X.PRESC_ID = Y.PRSC_NPI THEN X.PRESC_ID END AS PRESC_ID,
CASE WHEN NOT(X.PRESC_ID = Y.PRSC_NPI) THEN X.PRSC_NPI_N END AS PRSC_NPI_N

answered Aug 08 '20 at 00:03

Fred

1,916
1
8
16

Or is your requirement more complex than this? If so, please edit the question. – Fred Aug 08 '20 at 17:11
Thanks a lot Fred, I responded to your answer 2 days ago, i'm not sure why it's not here. I used the case statement you provided and I am getting the results I wanted. I've asked our senior developer to review my code. Thanks again, will update you once I get a reply from him – Bond Aug 10 '20 at 21:50

How do I compare two columns from two different tables

1 Answers1