I have two tables table a and table b with exact same structure. I would like to return the columns with different values: Could somebody tell me how to achieve the below
for eg;
Table A
ID, Fname, Lname, Age
1 Mike Smith 10
Table B
ID, Fname, Lname, Age
1 Mike Smith 20
My result should be
different_columns
Age
select id,
listagg(diff_col, ', ') within group(order by diff_col) as diff_cols
from (select a.id, 'fname' as diff_col
from tablea a
left join tableb b
on a.id = b.id
and a.fname = b.fname
where b.id is null
union all
select a.id, 'lname'
from tablea a
left join tableb b
on a.id = b.id
and a.lname = b.lname
where b.id is null
union all
select a.id, 'age'
from tablea a
left join tableb b
on a.id = b.id
and a.age = b.age
where b.id is null)
group by id
Fiddle: http://sqlfiddle.com/#!4/daeaf/4/0
(I added an ID # 2 with a different age AND lname, for illustration)
Another way of doing this is a giant case statement but I would prefer the above as it's easier to tack on more conditions should your actual table have more than 3 columns with which you are doing this test.
If you don't mind having one column per column check (see next fiddle) this might be more ideal for checking that many columns. I'm not sure there is an easier way; you'd have to tack on a bunch of ORs to the WHERE clause and a bunch more case statements for each column comparison.
select a.id,
case when a.fname <> b.fname then 'X' end as fname,
case when a.lname <> b.lname then 'X' end as lname,
case when a.age <> b.age then 'X' end as age
from tablea a
join tableb b
on a.id = b.id
where a.fname <> b.fname
or a.lname <> b.lname
or a.age <> b.age
Fiddle: http://sqlfiddle.com/#!4/daeaf/7/0
(this shows an X if there is a difference in the indicated column, otherwise nothing)
If you union both tables and group by id, then you'll know that any group having count(*) > 1 has at least 1 column that's different (since union automatically removes duplicates).
In order to determine which column is different, you can use count(distinct column_name) to see which columns have more than 1 distinct value.
select id,
count(distinct fname) fname,
count(distinct lname) lname,
count(distinct age) age
from (
select * from tablea
union
select * from tableb
) t1 group by id
having count(*) > 1
