From: Anupam Karmarkar
Date: May 20 2011 8:22am
Subject: Re: Help with a query
List-Archive: http://lists.mysql.com/mysql/225052
Message-Id: <575467.25997.qm@web94716.mail.in2.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1843374784-1305879779=:25997"

--0-1843374784-1305879779=:25997
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Aveek,

You need to use something like union all and having to get desire result

Follow example below

select file, digest=A0 from=20
(
SELECT file, digest,Count(*)=A0 as Cnt FROM A GROUP BY file, digest
union all
SELECT file, digest,Count(*)=A0 as Cnt FROM B GROUP BY file, digest
) tmp=20
group by file, digest
HAVING Sum(Cnt) <> (Select sum(refcount) from C WHERE tmp.file =3D C.file a=
nd tmp.digest =3D 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 (
=A0 =A0=A0=A0`file` varbinary(255) not null,
=A0 =A0=A0=A0`digest` binary(40) not null
)

CREATE TABLE B (
=A0 =A0=A0=A0`file` varbinary(255) not null,
=A0 =A0=A0=A0`digest` binary(40) not null
)

I have another table C of the following type=20

CREATE TABLE C (
=A0 =A0=A0=A0`file` varbinary(255) not null,
=A0 =A0=A0=A0`digest` binary(40) not null,
=A0 =A0 `refcount` bigint(20) not null
)

I need to write a query where for the same file and digest in table A and t=
able B, the refcount is table C is not the same. So:

SELECT COUNT(*) as count 1 FROM A WHERE file=3D'file1' AND digest=3D'digest=
1';
SELECT COUNT(*) as count 2 FROM B WHERE file=3D'file1' AND digest=3D'digest=
1';

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=3D'file1' AND digest=3D'digest1';

basically (refcount =3D=3D (count1 + count2)) should be true and I am inter=
ested 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:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dsb_akarmarkar@y=
ahoo.com


--0-1843374784-1305879779=:25997--