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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
