I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.
I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.
full outer join table2
on table1.PartID = table2.PartID
and table1.RaceID = table2.RaceID
where table1.PartID is null
or table2.PartID is null
I just gotta have more cowbell!
If it's not practically useful, then it's practically useless.
What about creating a UNION view consisting only of the two key attributes? SQL Server will prepare a nice QEP for quick selection; if you have a second view, that queries on NULL values for both fields in that view it will be faster than any outer join, maybe ;-)
, min(source) as source_table
, 'table1' as source
) as u
having count(*) between 0 and 1
note how MIN() will tell you which of the tables the unmatched row comes from
the HAVING clause won't ever actually find count(*)=0, i just threw that in there because of the cowbells