May 20, 2014 at 6:35 am
we have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
would like to have column names where the values are not matching.
May 20, 2014 at 6:45 am
I guess the first step whould be reading this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2014 at 7:03 am
declare @a as table (id int,name varchar(10), age int, city varchar(10), country varchar(10))
insert into @a(id,name,age,city,country)
select 100,'cat',21,'london','england'
union
select 101,'dog',22,'london','england'
union
select 102,'mouse',23,'london','england'
union
select 103,'rat',24,'london','england'
declare @b-2 as table (id int,name varchar(10), age int, city varchar(10), country varchar(10))
insert into @b-2(id,name,age,city,country)
select 100,'rat',21,'london','england'
union
select 101,'cat',22,'london','england'
union
select 102,'mouse',23,'london','england'
union
select 103,'rat',24,'london','england'
select a.* from @a a inner join @b-2 b on a.id=b.id
where a.name<>b.name or a.age<>b.age or a.city<>b.city or a.country<>b.country
so instead of column values can i get the column names with id's where mismatch is there.
May 20, 2014 at 7:10 am
If I'm reading it correctly, is this what you're after?
select a.id,
case when a.name = b.name then '' else 'name' end name,
case when a.age = b.age then '' else 'age' end age,
case when a.city = b.city then '' else 'city' end city,
case when a.country = b.country then '' else 'country' end country
from @a a
inner join @b-2 b on b.id = a.id
order by a.id;
May 20, 2014 at 7:15 am
cheers, this works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply