CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
ffert2907
ffert2907

Reputation: 370

Comparing 2 tables to check differences

I have 2 tables that i must compare to know what data are in or not. I try to find the query but can't find yet. The data to compare are stored in detail1 and detail2, defined like this :

detail1
D1_ID (integer)
D1_MASTERID (integer)

with data

| D1_MASTERID | D1_ID | 
|      1      |   1   | 
|      1      |   3   | 



detail2
D2_ID (integer)
D2_MASTERID (integer)

with data

| D2_MASTERID | D2_ID | 
|      1      |   1   | 
|      1      |   2   | 
|      1      |   4   |

and

master
MASTER_ID (integer)

with data

| MASTERID |
|     1    |

i tried many way, and this is the last try that still doesnn't work : 

select MASTER_ID, D1_ID, D2_ID
FROM master
LEFT JOIN detail1 ON MASTERID=D1_MASTERID
LEFT JOIN detail2 ON MASTERID=D2_MASTERID

The result i get is something like 

| MASTERID | D1_ID | D2_ID |
|    1     |   1   |   1   |
|    1     |   1   |   2   |
|    1     |   1   |   4   |
|    1     |   3   |   1   |
|    1     |   3   |   2   |
|    1     |   3   |   4   |

What i would like to get is :

| MASTERID | D1_ID | D2_ID |
|    1     |   1   |   1   |
|    1     |       |   2   |
|    1     |   3   |       |
|    1     |       |   4   |

I was thinking a left join can do so... but it's not working. or subequeries ? i'm really not sure

i checked that page http://sql.sh/cours/jointures but it doesn't helps also...

Thx

Upvotes: 2

Views: 100

Answers (2)

schurik
schurik

Reputation: 7928

In Oracle you can use FULL OUTER JOIN

select NVL(d1_masterid, d2_masterid) as masterid,  d1_id, d2_id
from   
        detail1
FULL OUTER JOIN
        detail2
on      d1_masterid = d2_masterid
and      d2_id =  d1_id
;

or if master could contains ids, which are not exists in both detailed tables:

with det as 
(
select NVL(d1_masterid, d2_masterid) as masterid,  d1_id, d2_id
from   
        detail1
FULL OUTER JOIN
        detail2
on      d1_masterid = d2_masterid
and      d2_id =  d1_id
)
select distinct m.masterid, d1_id, d2_id
from 
  master m
LEFT OUTER JOIN 
 det
on m.masterid = det.masterid

Upvotes: 3

Andomar
Andomar

Reputation: 238048

You could first query detail1 and the matching rows in detail2. Then add the list of detail2 rows that have no match in detail1:

select  master_id, d1_id, d2_id
from    master
left join
        detail1
on      master_id = d1_master_id
left join
        detail2
on      master_id = d2_master_id
        and d1_id = d2_id
union all
select  master_id, d1_id, d2_id
from    master
left join
        detail2
on      master_id = d2_master_id
left join
        detail1
on      master_id = d1_master_id
        and d1_id = d2_id
where   d1_id is null -- Row not found in d1

Example at SQL Fiddle.

Upvotes: 2

Related Questions