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 <aveekm@stripped> wrote:
From: Aveek Misra <aveekm@stripped>
Subject: Help with a query
To: "mysql@stripped" <mysql@stripped>
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=1
