CODEWITHSUNDEEP
sql-server
Ron
Ron

Reputation: 2503

Comparing data in two similar tables

I'm trying to compare two similar tables which have identical structures. So I execute the following sql:

select * from mainTable g1 left join comparisonTable g2 on g2.SettingName=g1.SettingName

(the tables columns are: ID, SettingName, SettingValue, TimeStamp)

And that query works OK, but I would like to constrain the results to only show rows where the G2.SettingValue is NULL or differs from G1.SettingValue.

So I amend the sql above to:

 select * from mainTable g1 left join comparisonTable g2 on g2.SettingName=g1.SettingName where g1.SettingValue <> g2.SettingValue OR g2.SettingValue Is NULL

HOWEVER, this only returns rows which satisfies the second condition. If I just run it without 

OR g2.SettingValue Is NULL

I get no rows at all. But many rows seem to satisfy this query. Yet, they don't appear.

This sql should illustrate the issue:

create table mainTable ([ID] [int] IDENTITY(1,1) NOT NULL, [settingname] [varchar](200) NULL, [settingvalue] [varchar](200))
create table comparisonTable ([ID] [int] IDENTITY(1,1) NOT NULL, [settingname] [varchar](200) NULL, [settingvalue] [varchar](200))
insert into maintable(SettingName, SettingValue) Values ('SettingName1','one'),('SettingName2','two'),('SettingName3','three')
insert into comparisonTable(SettingName, SettingValue) Values ('SettingName1','one'),('SettingName2','two'),('SettingName3','three')

Then run:

 select * from mainTable g1 full outer join comparisonTable g2 on g2.SettingName=g1.settingName

Then run:

insert into mainTable(SettingName, SettingValue) Values ('SettingName4','four')

Upvotes: 0

Views: 60

Answers (2)

Raul
Raul

Reputation: 3121

Try this:

 -- differing values    
SELECT g1.*
, g2.SettingValue as g2SettingValue
FROM mainTable g1
INNER JOIN comparisonTable g2
ON g1.SettingName = g2.SettingName
AND g1.SettingValue <> g2.SettingValue
UNION ALL
-- missing matches
SELECT g1.*
, NULL
FROM mainTable g1
WHERE NOT EXISTS (
    SELECT 1
    FROM comparisonTable g2
    WHERE g1.SettingName = g2.SettingName

Upvotes: 1

Tyron78
Tyron78

Reputation: 4187

The following should first create a subset of values, where mainTable and comparisonTable differ, and then performs the join to display the differences:

with cte(
  select * from mainTable
  EXCEPT
  select * from comparisonTable
)
select * from cte g1 left join comparisonTable g2 on g2.SettingName=g1.SettingName

Upvotes: 0

Related Questions