Help with a query
am 17.05.2011 09:53:40 von Aveek Misra
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=20
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 == (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@m.gmane.o rg
Re: Help with a query
am 20.05.2011 10:22:59 von Anupam Karmarkar
--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 wrote:
From: Aveek Misra
Subject: Help with a query
To: "mysql@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`file` varbinary(255) not null,
=A0 =A0`digest` binary(40) not null
)
CREATE TABLE B (
=A0 =A0`file` varbinary(255) not null,
=A0 =A0`digest` binary(40) not null
)
I have another table C of the following type=20
CREATE TABLE C (
=A0 =A0`file` varbinary(255) not null,
=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 == (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--
Re: Help with a query
am 20.05.2011 10:34:09 von Aveek Misra
--_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
inc.com>> wrote:
From: Aveek Misra >
Subject: Help with a query
To: "mysql@lists.mysql.com"
l.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 == (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@yahoo=
..com
--_000_3A535E57326947D3A153C10B3440E27Byahooinccom_--