SQL Query - How to and what am I doing wrong

Hello - to be open and honest I am writing up a second year University project. The sample code I have included is to create dummy data and to query this data stored in a database table and what I will talk about for the remainder of this post.

Here is the table and its content.

CREATE TABLE tblResult43(id NUMBER,value NUMBER);

CREATE INDEX idxResult43
 ON tblResult43(id);

CREATE UNIQUE INDEX idxResult43b
 ON tblResult43(id,value);

INSERT INTO tblResult43(id,value) VALUES (1,15);
INSERT INTO tblResult43(id,value) VALUES (1,231);
INSERT INTO tblResult43(id,value) VALUES (1,100);
INSERT INTO tblResult43(id,value) VALUES (1,67);
INSERT INTO tblResult43(id,value) VALUES (2,2);
INSERT INTO tblResult43(id,value) VALUES (2,16);
INSERT INTO tblResult43(id,value) VALUES (2,31);
INSERT INTO tblResult43(id,value) VALUES (2,231);
INSERT INTO tblResult43(id,value) VALUES (3,5);
INSERT INTO tblResult43(id,value) VALUES (4,2);
INSERT INTO tblResult43(id,value) VALUES (4,16);
INSERT INTO tblResult43(id,value) VALUES (5,231);
INSERT INTO tblResult43(id,value) VALUES (5,15);
INSERT INTO tblResult43(id,value) VALUES (5,61);
INSERT INTO tblResult43(id,value) VALUES (5,19);
INSERT INTO tblResult43(id,value) VALUES (6,15);
INSERT INTO tblResult43(id,value) VALUES (6,231);
INSERT INTO tblResult43(id,value) VALUES (7,54);
INSERT INTO tblResult43(id,value) VALUES (7,67);
INSERT INTO tblResult43(id,value) VALUES (11,15);
INSERT INTO tblResult43(id,value) VALUES (11,231);
INSERT INTO tblResult43(id,value) VALUES (11,67);
INSERT INTO tblResult43(id,value) VALUES (11,100);
INSERT INTO tblResult43(id,value) VALUES (11,234);
INSERT INTO tblResult43(id,value) VALUES (12,15);
INSERT INTO tblResult43(id,value) VALUES (12,231);
INSERT INTO tblResult43(id,value) VALUES (12,233);
INSERT INTO tblResult43(id,value) VALUES (12,67);
INSERT INTO tblResult43(id,value) VALUES (12,100);
INSERT INTO tblResult43(id,value) VALUES (12,200);

What I want to do is to retrieve the ID's for entries in tblResult43 that have values 15, 231, 100, and 67 but also have no other values (by this I mean that in the sample data id=1 is valid because it has 15, 231, 100, and 67; id=12 is not valid because even though it contains 15, 231, 100, 67 it also contains 200 and 233; and id=11 is not valid because if contains 15, 231, 100, 67 BUT AN ADDITIONAL 234; and all the others are not valid because they do not contain all the numbers 15, 231, 100, and 67).

The query I have come up with so far is:

 SELECT t1.id
   FROM tblResult43 t1, tblResult43 t2,

        tblResult43 t3, tblResult43 t4

    WHERE t1.id=t2.id
      AND t1.id=t3.id
      AND t1.id=t4.id
      AND t1.value=15
      AND t2.value=231
      AND t3.value=100
      AND t4.value=67
      AND NOT EXISTS (SELECT id
                       FROM tblResult43
                        WHERE t1.id=id
                          AND ROWID NOT IN (t1.ROWID, t2.ROWID, t3.ROWID,
t4.ROWID)
                          AND value NOT IN (15,231,100,67)
                     );

This query works really well and gives me the answer I am wanting (ie id=1) confirming I am approaching the logic well. Unfortunately it runs really slow (although the sample table data I have included above doesn't contain many entries, the sample data provided by the tutor contains about 10,000 rows and my query is taking over an hour to execute on my home computer).

I have obviously approached the problem incorrectly because of the time my query is taking to execute. But I cannot see an alternative. Could someone not give me the answer but point me in the right direction on how I should be constructing my query to improve the speed a bit.

Thank you for thinking about my question. Jenny
Leeds, UK

(ps. The University is using Oracle 8i on a Windows NT network, at home I am using Oracle 8i personal on a Windows Xp computer). Received on Sat Nov 08 2003 - 06:20:18 CST