CODEWITHSUNDEEP
sqldatabasejoinoracle11g
Suresh Rajagopal
Suresh Rajagopal

Reputation: 183

oracle 11g sql query to get data from two tables joining

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?

Upvotes: 2

Views: 2615

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Here is one way to do this, by taking everything from Actual and then just things in Estimate that aren't in Actual:

select a.*
from Actual a
union all
select e.*
from Estimate e
where not exists (select 1 from actual a where a.sal_id = e.sal_id and a.gal_id = e.gal_id) and
      e.process_flag = 'N'

This may not be the most efficient approach if you have a large amount of data. But it should work fine on smaller data sets.

Upvotes: 0

valex
valex

Reputation: 24144

Here is a SQLFiddle example

select sal_id,gal_id,actual_id,null estimate_id,amount,tax from actual where process_flag='N' 
union all
select sal_id,gal_id,null actual_id,estimate_id,amount,tax from estimate where process_flag='N' and
(sal_id,gal_id) not in (select sal_id,gal_id from actual where process_flag='N')

Upvotes: 2

Related Questions