compare values between 2 tables (10+ fields)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

compare values between 2 tables (10+ fields)

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-08-18 : 15:50:14

select id, updated, slip_stage_id, type, rate, hour, minute, total, cost, customer_id, project_id,invoice_id, project_task_id, custom_16, custom_17
from slip
where id = '33583'

select id, updated, slip_stage_id, type, rate, hour, minute, total, cost, customer_id, project_id,invoice_id, project_task_id, custom_16, custom_17
from slip_8_11_8
where id = '33583'

Say I have these two tables. I want to make sure these fields value are same between these tables (id=id). How do I do that with just one statement?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-18 : 17:45:35

This will return all rows that do not have a matching row or there is any column that does not match.


select
	a.*,
	b.*
from
	slip a
	full outer join
	slip_8_11_8 b
	on a.ID = b.ID
where
	0 <>
	case
	when	a.ID is null or b.ID is null
	then	1
	else	0 end+
	case
	when	(a.updated is null and b.updated is not null ) or
		(a.updated is not null and b.updated is null )
	then	1
	when	a.updated <> b.updated
	then 	1
	else	0 end+
	case
	when	(a.slip_stage_id is null and b.slip_stage_id is not null ) or
		(a.slip_stage_id is not null and b.slip_stage_id is null )
	then	1
	when	a.slip_stage_id <> b.slip_stage_id
	then 	1
	else	0 end+
	... rest of columns ...

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources