List:General Discussion« Previous MessageNext Message »
From:James Eaton Date:February 12 2008 12:27am
Subject:Re: Query two different databases for differences
View as plain text  
From: Peter Brawley

> >I'd like to run a query to find the records that
> >are present in one database but not the other.
>
> See 'Compare data in two tables' at 
> http://www.artfulsoftware.com/infotree/queries.php.


Thanks.  That's a start.


SELECT
  MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
  SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
  FROM a
  UNION ALL
  SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
  FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;


This finds common rows.  That doesn't help when the tables have about 
20,000 rows and most are the same.  How do I invert the query so that I 
can find the 'uncommon' rows?  Second, the primary key ('id' in the 
example) values do not match, so how/where in the query can you specify 
how to match rows from the two tables using other columns? 

Thread
Query two different databases for differencesJames Eaton11 Feb
  • Re: Query two different databases for differencesPeter Brawley12 Feb
  • Re: Query two different databases for differencesJames Eaton12 Feb
    • Re: Query two different databases for differencesBJ Swope12 Feb
    • Re: Query two different databases for differencesPeter Brawley12 Feb