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" <bbellow_at_chi.navtech.com> 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: gabriel_gap_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Nov 17 2003 - 16:54:25 CST
