List:General Discussion« Previous MessageNext Message »
From:Aveek Misra Date:May 20 2011 8:34am
Subject:Re: Help with a query
View as plain text  
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
<aveekm@stripped<mailto:aveekm@stripped>> wrote:

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




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