CODEWITHSUNDEEP
sqldatabaseoracle-databasejoin
Suresh Rajagopal
Suresh Rajagopal

Reputation: 183

oracle sql query to get data from two tables of similar type

I have two tables ACTUAL AND ESTIMATE having unique column(sal_id, gal_id, amount, tax).

In ACTUAL table I have actual_id, sal_id, gal_id, process_flag, amount, tax
1 111 222 N 100 1
2 110 223 N 200 2

In ESTIMATE table I have estimate_id, sal_id, gal_id, process_flag, amount, tax
3 111 222 N 50 1
4 123 250 N 150 2 5 212 312 Y 10 1

Now I want a final table, which should have record from ACTUAL table and if no record exist for sal_id+gal_id mapping in ACTUAL but exist in ESTIMATE, then populate estimate record (along with addition of amount and tax).

In FINAL table
id sal_id, gal_id, actual_id, estimate_id, total
1 111 222 1 null 101 (since record exist in actual table for 111 222)
2 110 223 2 null 202 (since record exist in actual table for 110 223)
3 123 250 null 4 51 (since record not exist in actual table but estimate exist for 123 250)

(for 212 312 combination in estimate, since record already processed, no need to process again).

I am using Oracle 11g. Please help me on writing a logic in a single sql query?

Thanks.

Upvotes: 1

Views: 1792

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

There are several ways to write this query. One way is to use join and coalesce:

select coalesce(a.sal_id, e.sal_id) as sal_id,
       coalesce(a.gal_id, e.gal_id) as gal_id,
       coalesce(a.actual_value, e.estimate_value) as actual_value
from actual a full outer join
     estimate e
     on a.sal_id = e.sal_id and
        a.gal_id = e.gal_id

This assumes that sal_id/gal_id provides a unique match between the tables.

Since you are using Oracle, here is perhaps a clearer way of doing it:

select sal_id, gal_id, actual_value
from (select *,
             max(isactual) over (partition by sal_id, gal_id) as hasactual
      from ((select 1 as isactual, *
             from actual
            ) union all
            (select 0 as isactual, *
             from estimate
            )
           ) t
     ) t
where isactual = 1 or hasactual = 0

This query uses a window function to determine whether there is an actual record with the matching sal_id/gal_id. The logic is to take all actuals and then all records that have no match in the actuals.

Upvotes: 1

Related Questions