ffert2907 ffert2907 - 2 months ago 7
SQL Question

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

Answer

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.

Comments