Select param when not null or another param-CodePudding

SELECT COALESCE(a.task_id,b.task_id) as 
task_id,b.task_name as name
FROM table_a as a FULL OUTER JOIN table_b as b ON 
a.task_id = b.task_id

If b.task_name is null It will be

SELECT COALESCE(a.task_id,b.task_id) as 
task_id,a.task_name as name
FROM table_a as a FULL OUTER JOIN table_b as b ON 
a.task_id = b.task_id

How to add where to select param from table a if not null or from another table if its be null when using fullouter join

Table_a: primary_id,task_id, task_name = [{1,1,"name1ofa"}, {2,2,"name2ofa"}]
Talbe_b: primary_id,task_id, task_name = [{1,1,"name1ofb"}, {2,2,null}]
Expect selected query: [{1,"name1ofb"}, {2,"name2ofa"}] I want is the priority of b.task_name to be higher than a.taske_name

CodePudding user response：

You can try to use INNER JOIN instead of FULL OUTER JOIN because you want to get a result that matches task_id from those tables.

Then COALESCE function will return the first non-null value in parameters, so you might use b.task_name be the first parameter

SELECT a.task_id , 
       COALESCE(b.task_name,a.task_name) as name
FROM table_a as a 
INNER JOIN table_b as b 
ON a.task_id = b.task_id