left join if condition met , set to null-CodePudding

I am trying to write a sql join query for below tables.

I am trying get as in expected table.

The only join condition is that , if In table 1, the value of operation is 'D' i.e deleted, then the corresponding value of rows from table 2 should be null.

DB: POSTGRES

Table 1

id | operation| keys|
-----------------------
1  | I        | 1   |
2  | U        | 2   |
3  | D        | 3   |

Table 2

id |keys| col1| col2
-----------------------
1  | 1  | xyz | abc
2  | 2  | yxz | cba
3  | 3  | zxy | bac

Expected Output

id | operation| keys| col1| col2
---------------------------------
1  | I        | 1   | xyz |abc
2  | U        | 2   | yxz |cba
3  | D        | 3   | NULL|NULL

CodePudding user response：

I think you can just write a left join statement with the right conditions, in this case table_1.keys = table_2.keys and table_1.operation != 'D'. This will join the second table and you will get null in the columns where the condition was false.

select t1.*, t2.col1, t2.col2  from table_1 as t1 left join table_2 as t2 on (t1.keys = t2.keys and t1.operation != 'D')

See demo at DBfiddle

CodePudding user response：

You can get your desired result using CASE WHEN. Basically, the query will look like this:

SELECT t1.id, t1.operation, t1.keys,
CASE WHEN t1.operation = 'D' THEN NULL ELSE t2.col1 END col1,
CASE WHEN t1.operation = 'D' THEN NULL ELSE t2.col2 END col2
FROM table1 t1 
LEFT JOIN table2 t2 ON t1.id = t2.id
AND t1.keys = t2.keys;

I don't know if you really want to do a left join or if you need an inner join instead. And I don't know if you will join on both id and keys columns or on one of them only. So the query will produce the expected result for your example, but you have to check these things, please. A last note: You could of course even just remove the "CASE WHEN" construct and put the != 'D' condition in the left join. I did not propose this since the "CASE WHEN" construct easy allows you to select different values for col1 and col2 if necessary, so it's more flexible.