Re: SQL Query - How to and what am I doing wrong

Instead of self-joining four times, you can do it in a simpler way by using "group by", "having", "count", and "decode".

Read up on COUNT at
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions26a.htm#82699 and on DECODE at
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions33a.htm#1017439

You'll notice that count(expr) returns the number of rows where expr is not null.

So what you can do is use decode to convert all the values you don't want to include in the count to null, and convert all the values you want to include the count to some number like 1.

For example, if you want to select all the values of id where there's at least one row with value=15, here's how you can do it:

select id
from tblResult43
group by value
having count(decode(value, 15, 1, null)) > 0;

If you want to count the number of rows where value is not equal to any of (15, 231, 100, 67), then it's the exact opposite: you convert 15, 231, 100, 67 to null, and everything else to 1, and then do a count.

This way you can write a query like:

select id
from tblResult43
group by value
having ...

and ...
and ...
and ...
and ...

to do what you want.

One thing you have to be careful (and also you have to decide) is what to do if an id has rows where value is null. Do you want to include that id or not. Depending on this, the query would be slightly different.

Hope I'm not giving away too much of the answer. Have fun!

• Dave

"Atkins, Jenifer" <atkinsjennifer_246_at_hotmail.com.spamremove> wrote in message news:1068294030.64211.0_at_damia.uk.clara.net...
> 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 - 12:32:44 CST