I'm sure there is an easy answer to this, but it's Friday evening
here...
I have table A, with field Cid, and table C, whose primary key is Cid.
I have table B which contains a subset of the IDs of table A, Aid.
I need to produce a *count* of items in C for which certain criteria
in A are true, among them that only items in both B and A can be
considered (equijoin B to A).
At the moment, I am doing
CREATE TABLE C (Cid int4 primary key,cname text);
INSERT INTO C VALUES (1,'I am 1');
INSERT INTO C VALUES (2,'I am 2');
INSERT INTO C VALUES (3,'I am 3');
CREATE TABLE A (Aid int4 primary key,somefield int4,otherfield
int4,Cid int4 not null);
INSERT INTO A VALUES(1,1,2,1);
INSERT INTO A VALUES(2,1,3,1);
INSERT INTO A VALUES(3,1,2,1);
INSERT INTO A VALUES(4,1,2,2);
INSERT INTO A VALUES(5,1,3,2);
INSERT INTO A VALUES(6,1,2,3);
INSERT INTO A VALUES(7,1,2,3);
CREATE TABLE B (Aid int4,Bid int4 primary key);
INSERT INTO B VALUES (1,1);
INSERT INTO B VALUES (2,2);
INSERT INTO B VALUES (3,3);
INSERT INTO B VALUES (4,4);
INSERT INTO B VALUES (5,5);
SELECT Count(Cid) FROM C WHERE EXISTS(SELECT A.Aid FROM A,B WHERE
A.Cid=C.Cid AND B.Aid=A.Aid AND A.somefield=1 AND A.otherfield=2);
This gives the correct answer, i.e.2.
There must be something more elegant (and faster), but my experiments
with COUNT, WHERE and DISTINCT were not a success.
Thanks,
Moray
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
