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

"Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:gFarb.3171$rO4.666_at_newssvr23.news.prodigy.com...
> 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;

Sorry there's a typo. This query should be:

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

Cheers,
Dave

>
> 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:38:39 CST