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

[ad_1]

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 | 

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

[ad_2]

لینک منبع