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

 > This finds common rows.

Eh!? ... HAVING COUNT(*)=1 returns ONLY pairs that are different:

drop table if exists a,b;
create table a(i int,j int,k int);
insert into a values(1,10,100),(2,20,200),(3,30,300);
create table b select * from a;
update b set k=301 where k=300;
select * from a;
+------+------+------+
| i    | j    | k    |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   30 |  300 |
+------+------+------+
select * from b;
+------+------+------+
| i    | j    | k    |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   30 |  301 |
+------+------+------+
SELECT
  MIN(TableName) as TableName, i,j,k
  FROM (
    SELECT 'Table a' as TableName, a.i, a.j, a.k
    FROM a
    UNION ALL
    SELECT 'Table b' as TableName, b.i, b.j, b.k
    FROM b
) AS tmp
GROUP BY i,j,k
HAVING COUNT(*) = 1
ORDER BY i;
+-----------+------+------+------+
| TableName | i    | j    | k    |
+-----------+------+------+------+
| Table a   |    3 |   30 |  300 |
| Table b   |    3 |   30 |  301 |
+-----------+------+------+------+

PB

-----

James Eaton wrote:
> 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