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
Go to Top of Page
   

- Advertisement -