Simple way to find the differences 1

[markros]

Hi,

I have two tables - one has

ConfigID CategoryID FeeDescription Amount Taxable Selected

fields

I have a subset for particular ConfigID and CategoryID in another table.

I'm thinking what would be the easiest way to compare them both and update the main table based on the changes in the subset table, which may have some records added, some deleted and some changed. I'm guessing I would need to related them somehow based on the FeeDescription field.

I'm asking this question here for some ideas.

Thanks in advance.

 

[ousoonerjoe]

Look at something like:

SELECT id FROM Table1 WHERE Field2 NOT IN (SELECT id FROM Table2)

This is a very simplistic model to follow, but it may get you in the right direction.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[markros]

This is to get records in one table and not in anoher. I actually prefer LEFT JOIN check, it performs better. My main concern was in identifying the records that was changed. I think I would need to get 3 different results (new, deleted, changed) and work from them.

 

[PDreyer]

To identify differences

select ConfigID, CategoryID
,case sum(i)
  when 2 then 'Changed'
  when 3 then 'Deleted'
  when 4 then 'New    '
 end
from (
select a.*, i=case when b.ConfigID is null then 3 else 1 end
from #main a
left join #subset b 
  on a.ConfigID=b.ConfigID
 and a.CategoryID=b.CategoryID
union 
select b.*, i=case when a.ConfigID is null then 4 else 1 end
from #main a
right join #subset b 
  on a.ConfigID=b.ConfigID
 and a.CategoryID=b.CategoryID
)t
group by ConfigID, CategoryID
having sum(i)>1

or

select 
case when b.ConfigID is null then 'Deleted'
else case when a.ConfigID is null then 'New'
else 'changed' end end
,*
from main a
full outer join subset b 
  on a.ConfigID=b.ConfigID
 and a.CategoryID=b.CategoryID
where a.FeeDescription!=b.FeeDescription
   or a.Amount!=b.Amount
   or a.Taxable!=b.Taxable
   or a.Selected!=b.Selected
   or a.ConfigID is null
   or b.ConfigID is null

 

[markros]

Thanks a lot, I'm going to implement something very close to your suggestion.