CODEWITHSUNDEEP
sqldatabaserdbms
Will Mcavoy
Will Mcavoy

Reputation: 495

Need to find the difference between the tables

I have table structure as follow : enter image description here

I need to find the difference between the tables as what data which is not available in another table(vice versa). I can able to find the difference as follow :

enter image description here

Sql Query used :

select * 
from (select input_name_id, count(1) as cnt
      from Table1
      group by input_name_id
     ) a join 
     (select input_name_id, count(1) as cnt
      from Table2
      group by input_name_id
     ) b
     on (a.input_name_id = b.input_name_id)
where a.cnt <> b.cnt

Expected outcome :

enter image description here

I have tried number ways to pull the data but I couldn't! So your help much appreciated. Thanks

Upvotes: 1

Views: 59

Answers (2)

Arockia Nirmal
Arockia Nirmal

Reputation: 777

simple solution using except and union all

select null as a_input_name_id, null as a_matchid, null as a_name, input_name_id as b_input_name_id, matchid as b_matchid, name as b_name
from
(select input_name_id, matchid, name, row_number() over (partition by input_name_id, matchid, name order by input_name_id) as rw_num
from t2
except
select input_name_id, matchid, name, row_number() over (partition by input_name_id, matchid, name order by input_name_id) as rw_num
from t1 ) a


union all

select input_name_id as a_input_name_id, matchid as a_matchid, name as a_name, null as b_input_name_id, null as b_matchid, null as b_name
from
(select input_name_id, matchid, name, row_number() over (partition by input_name_id, matchid, name order by input_name_id) as rw_num
from t1
except
select input_name_id, matchid, name, row_number() over (partition by input_name_id, matchid, name order by input_name_id) as rw_num
from t2) b

enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Two things: (1) full outer join; (2) enumerate the rows with the same values:

select * 
from (select input_name_id, match_id, name, 
             row_number() over (partition by input_name_id, match_id, name order by name) as seqnum
      from Table1
     ) a full join 
     (select input_name_id, match_id, name, 
             row_number() over (partition by input_name_id, match_id, name order by name) as seqnum
      from Table2
     ) b
     on a.input_name_id = b.input_name_id and
        a.match_id = b.match_id and
        a.name = b.name and
        a.seqnum = b.seqnum
where a.seqnum is null or b.seqnum is null;

Upvotes: 1

Related Questions