CODEWITHSUNDEEP
sql
Harryy
Harryy

Reputation: 41

Compare a column between two tables and add a new column with a integer wherever they match using sql

I have two tables: table1 and table2. I want to compare dx1 from table1 with code in table2, if they match I want to create a new column called 'newversion' in dersiredoutout table and populate that particular row with 9. I have tried the code below but it doesn't work :(

Table1:

version   |    dx1
----------+----------
null      |   uuu98
null      |   asdf0
null      |   mnbv9

Table2:

code  |   description
------+---------------
asdf0 |   tadatada

Desired output:

    version  |   dx1   | newversion
    ---------+---------+------------
    null     | uuu98   |
    null     | asdf0   |     9
    null     | mnbv9   |

My SQL:

CREATE OR REPLACE TEMP VIEW desiredoutput AS (
    SELECT * 
    FROM
        (SELECT 
             version, dx1, 
             CASE
                WHEN (SELECT dx1 
                      FROM table1 
                      WHERE (dx1 IN (SELECT code FROM table2)) AND version IS NULL)  
                   THEN 9
             END as newversion
         FROM 
             table1)
    )

Upvotes: 0

Views: 33

Answers (3)

zarruq
zarruq

Reputation: 2465

Another approach can be to compare t2.code column to t1.dx1 using case as below.

SELECT t1.version,
       t1.dx1,
       CASE
           WHEN t1.dx1 = t2.code THEN 9
       END AS newversion
FROM table1 t1
LEFT JOIN table2 t2 
ON t1.dx1 = t2.code;

DEMO

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

You can do this via a left join:

SELECT
    t1.version,
    t1.dx1,
    CASE WHEN t2.code IS NOT NULL THEN 9 END AS newversion
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.dx1 = t2.code

Follow the link below for a demo in MySQL (you never told us which database you are using).

Demo

Upvotes: 2

Alan
Alan

Reputation: 1428

If you only want to display the additional NewVersion column in the output of the SELECT statement then 

SELECT
    Table1.Version,
    Table1.Description,
    CASE WHEN Table2.code is NULL then NULL ELSE 9 END as NewVersion
FROM 
    Table1
    LEFT JOIN Table2 ON Table1.dx1 = Table2.code

should work for you.

Upvotes: 2

Related Questions