Compare fields in 2 tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Compare fields in 2 tables

Author

Topic

o9z
Starting Member

23 Posts

Posted - 2008-01-30 : 08:36:21

For example, I have 2 tables that have very similar fields

Table 1 Table 2
ShipRpt ShipRpt
invoice invoice
total sold total sold

Is it possible to compare each of these fields and return any record that has non matching data? I would join the tables on the shipping report or invoice and only compare the total sold field. If the invoices match and the total sold is different in each table, can I just return the 1 record?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-30 : 08:44:07

select *
from table1 a
join table2 b
on a.invoice=b.invoice
and a.[total sold]<>b.[total sold]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 08:44:21

SELECT ShipRpt, Invoice
FROM (
SELECT ShipRpt, Invoice, TotalSold, 'Table1' AS TableNam FROM Table
UNION ALL
SELECT ShipRpt, Invoice, TotalSold, 'Table2' FROM Table
) AS x
GROUP BY ShipRpt, Invoice
HAVING COUNT(*) <> 2
OR sum(case when tablename = 'table1' then totalsold else 0 End) <> sum(case when tablename = 'table2' then totalsold else 0 End)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources