List:General Discussion« Previous MessageNext Message »
From:Anupam Karmarkar Date:May 20 2011 8:22am
Subject:Re: Help with a query
View as plain text  
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);


--- 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

     `file` varbinary(255) not null,
     `digest` binary(40) not null

     `file` varbinary(255) not null,
     `digest` binary(40) not null

I have another table C of the following type 

     `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.

MySQL General Mailing List
For list archives:
To unsubscribe:

Help with a queryAveek Misra17 May
  • Re: Help with a queryAnupam Karmarkar20 May
    • Re: Help with a queryAveek Misra20 May