CODEWITHSUNDEEP
sqldatabaseoracle-database
Vinícius Affonso
Vinícius Affonso

Reputation: 85

How can i merge 2 duplicate Tables with different values?

I want to consolidate 2 tables into one (table or view). Both have the exact columns, but diffent values, like:

Table 1:

id    Doc1             Doc2
---------------------------------
1     12/06/2016       NULL
2     20/08/2016       15/03/2016
3     NULL             NULL

Table 2:

id    name    desc
-----------------------
1     NULL      1
2     NULL      NULL
3     1         0

The logic I want to use is this:

If value on Table 1 is a date (not NULL) then 'YES'

If value on Table 1 is NULL, check Table 2

If value on Table 2 is 1 then 'NA'

If value on Table 2 is 0 or NULL then 'NO'

So the result table will be something like this:

Table Result:

id    Doc1    Doc2
-----------------------
1     YES      NA
2     YES      YES
3     NA       NO

How can I do this?

Upvotes: 1

Views: 50

Answers (1)

user5683823
user5683823

Reputation:

Something like this:

select t1.id as id,
       case when t1.doc1 is not null then 'YES'
            when t2.name != 0        then 'NA'
                                     else 'NO' end as doc1,
       case when t1.doc2 is not null then 'YES'
            when t2.desc != 0        then 'NA'
                                     else 'NO' end as doc2
from table_1 t1 full outer join table_2 t2 on t1.id = t2.id
;

This takes advantage of the "short-circuit evaluation" of case expressions - the first when that evaluates to TRUE sets the value of the expression and the remaining branches (if any) are skipped. !=0 implies not null, by the way.

Upvotes: 1

Related Questions