Ok, guys I have to apologize twice,
First: the delay to answer your very kind emails, (you
know the urgent problems dont let you time for the
important ones)
Second: maybe my question was not clear enough,
ciertanly what Bambi says is what I need "give me all
the usrs where there exists a record containing 1 AND
5 AND 7", the criteria was the list, not the records,
so it does not matter if the user has many more
records, but if he/she has those records that are in
the list, that is what I want, the solution is as
simple as Bambi's query.
I really really appreciate all the solutions provided
for you guys, I swear I tested every one.
Below I have a copy of my original email.
Thank you very much!
Gabriel Aragon
+++++++++++++++++++++++++++++++++++++++++++
I have a table like this:
Usr val
----------
GAP 1
GAP 5
GAP 7
JKL 8
JKL 5
I need a query that returns the user (GAP o JKL) that
has ALL the values in a list. Example: Having the
list: 1,5,7 the result will be GAP, but with the
values 1,5 or 1,5,7,8 there will be no result.
select distinct usr from xxx
where val = All (1,3,5)
I was trying the ALL operator but it works with part
of the list, I need the user that has (exactly) all
the values in the list. Any idea?
Maybe it's a simple solution, but after several hours
I feel blocked.
Gabriel
+++++++++++++++++++++++++++++++++++++++++++
--- "Bellow, Bambi" <[EMAIL PROTECTED]> wrote:
> Jacques --
>
> Huh? I thought the question was, "give me all the
> usrs where there exists a
> record containing a 1,5, and 7". How can that
> return one record, when there
> are two users -- and only two users -- who have the
> 1,5,7 combination? The
> data provided shows that both GAP and GPA have a 1,
> 5 and 7 and that no
> other users do. My query provides that answer. If
> that wasn't the
> question, then that won't be the answer; but if it
> *was* the question, then
> the query is correct.
>
> HTH,
> Bambi.
> =====================
>
> create table gab (usr char(3),val number);
> insert into gab values ('GAP',1);
> insert into gab values ('GAP',5);
> insert into gab values ('GAP',5);
> insert into gab values ('GAP',7);
> insert into gab values ('PAG',1);
> insert into gab values ('PAG',7);
> insert into gab values ('PAG',2);
> insert into gab values ('JKL',1);
> insert into gab values ('JKL',5);
> insert into gab values ('JKL',5);
> insert into gab values ('GPA',1);
> insert into gab values ('GPA',5);
> insert into gab values ('GPA',7);
> insert into gab values ('GPA',8);
>
> select usr from gab
> where val=1
> intersect
> select usr from gab
> where val=5
> intersect
> select usr from gab
> where val=7;
>
> USR
> ---
> GAP
> GPA
>
>
>
> -----Original Message-----
> Sent: Friday, November 14, 2003 7:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
> > -----Original Message-----
> > Bellow, Bambi
> >
> > Why not do it like this...
> >
> > select usr from gab
> > where val=1
> > intersect
> > select usr from gab
> > where val=5
> > intersect
> > select usr from gab
> > where val=7;
>
>
> Because that way you would get the wrong answer.
> With the sample data as
> kindly provided by Mr. Begun the correct query would
> return one row, but
> your query returns two rows.
> SQL> select * from gab ;
>
> USR VAL
> ---------- ---------
> GAP 1
> GAP 5
> GAP 5
> GAP 7
> PAG 1
> PAG 7
> PAG 2
> JKL 1
> JKL 5
> JKL 5
> GPA 1
> GPA 5
> GPA 7
> GPA 8
>
> 14 ligne(s) sélectionnée(s).
>
> SQL> select usr from gab
> 2 where val=1
> 3 intersect
> 4 select usr from gab
> 5 where val=5
> 6 intersect
> 7 select usr from gab
> 8 where val=7;
>
> USR
> ----------
> GAP
> GPA
> --
=====
"Any dream worth having is a dream worth fighting for"(Cualquier sueño que valga la
pena tener, es un sueño por el que vale la pena luchar)Charles Xavier
__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gabriel Aragon
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
