I am trying to compare number of rows of two tables in two databases. The number of rows should be the same :
SELECT
(SELECT COUNT(*) FROM db1.table1)-
(SELECT COUNT(*) FROM db2.table1)
AS difference
How do i select only if difference<>0? I need to run this for multiple tables and i don't need 0 values.
I could load results in C# list and sort it out but i'd like to finish all in query.
I've tried using information_schema.TABLES for this but it's not suitable because it returns approximate results.
I'm using Mysql 5.5
解决方案
SELECT * FROM
(SELECT
(SELECT COUNT() FROM db1.table1) -
(SELECT COUNT() FROM db2.table1)
AS t, "table1"AS table) X
WHERE x.t1<>0
UNION ALL
SELECT * FROM
(SELECT
(SELECT COUNT() FROM db1.table2) -
(SELECT COUNT() FROM db2.table2)
AS t, "table2"AS table) X
WHERE x.t<>0