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 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: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

--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
(
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
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 [at] yahoo-inc.com> wrote:

From: Aveek Misra <aveekm [at] yahoo-inc.com>
Subject: Help with a query
To: "mysql [at] lists.mysql.com" <mysql [at] 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 (
=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

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 [at] y=
ahoo.com


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

--_000_3A535E57326947D3A153C10B3440E27Byahooinccom_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I eventually came up with a solution myself although the query is a bit dif=
ferent

SELECT C.file, C.digest, (a.cnt_A + b.cnt_B) AS total_count, C.refcount FR=
OM 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 =3D a.file and C.digest =3D a.digest and C.file =3D b.file and=
C.digest =3D 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 =3D C.file a=
nd tmp.digest =3D C.digest);

--Anupam


--- On Tue, 17/5/11, Aveek Misra <aveekm [at] yahoo-inc.com<mailto:aveekm [at] yahoo-=
inc.com>> wrote:

From: Aveek Misra <aveekm [at] yahoo-inc.com<mailto:aveekm [at] yahoo-inc.com>>
Subject: Help with a query
To: "mysql [at] lists.mysql.com<mailto:mysql [at] lists.mysql.com>" <mysql [at] lists.mysq=
l.com<mailto:mysql [at] 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 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: http://lists.mysql.com/mysql?unsub=3Dsb_akarmarkar [at] yahoo=
..com




--_000_3A535E57326947D3A153C10B3440E27Byahooinccom_--