I eventually came up with a solution myself although the query is a bit different
SELECT C.file, C.digest, (a.cnt_A + b.cnt_B) AS total_count, C.refcount FROM C, (SELECT file, digest, COUNT(file) AS cnt_A FROM A GROUP BY file, digest) as a, (SELECT file, digest, COUNT(file) AS cnt_B FROM B GROUP BY file, digest) as b WHERE C.file = a.file and C.digest = a.digest and C.file = b.file and C.digest = b.digest and C.refcount <> (a.cnt_A + b.cnt_B); Thanks Aveek On May 20, 2011, at 1:52 PM, Anupam Karmarkar wrote: Hi Aveek, You need to use something like union all and having to get desire result Follow example below select file, digest from ( SELECT file, digest,Count(*) as Cnt FROM A GROUP BY file, digest union all SELECT file, digest,Count(*) as Cnt FROM B GROUP BY file, digest ) tmp group by file, digest HAVING Sum(Cnt) <> (Select sum(refcount) from C WHERE tmp.file = C.file and tmp.digest = C.digest); --Anupam --- On Tue, 17/5/11, Aveek Misra <ave...@yahoo-inc.com<mailto:ave...@yahoo-inc.com>> wrote: From: Aveek Misra <ave...@yahoo-inc.com<mailto:ave...@yahoo-inc.com>> Subject: Help with a query To: "mysql@lists.mysql.com<mailto:mysql@lists.mysql.com>" <mysql@lists.mysql.com<mailto:mysql@lists.mysql.com>> Date: Tuesday, 17 May, 2011, 1:23 PM I have a table A and table B of the same type as CREATE TABLE A ( `file` varbinary(255) not null, `digest` binary(40) not null ) CREATE TABLE B ( `file` varbinary(255) not null, `digest` binary(40) not null ) I have another table C of the following type CREATE TABLE C ( `file` varbinary(255) not null, `digest` binary(40) not null, `refcount` bigint(20) not null ) I need to write a query where for the same file and digest in table A and table B, the refcount is table C is not the same. So: SELECT COUNT(*) as count 1 FROM A WHERE file='file1' AND digest='digest1'; SELECT COUNT(*) as count 2 FROM B WHERE file='file1' AND digest='digest1'; and then adding up the two counts from these queries and comparing it with the result of the following query: SELECT refcount FROM C WHERE file='file1' AND digest='digest1'; basically (refcount == (count1 + count2)) should be true and I am interested in finding out all such records in table C where this is not the case. Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com
