Mr. Begun: I'm not convinced that your answer is quite the right one. I tried INSERT INTO gab VALUES ('GAP', 9) ; and then this query SELECT usr FROM ( SELECT DISTINCT usr, val FROM gab ) WHERE val IN (1, 5, 7) GROUP BY usr HAVING COUNT(*) = 3 -- number of elements in the list / returned the value 'GAP' even though 'GAP' has 4 "val"s in the table. The HAVING COUNT (*) = should also match the number of distinct rows for usr.
Inspired by Tom Kyte's answer "varying elements in IN list" http://asktom.oracle.com/pls/ask/f?p=4950:8:8788433637134280281::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061, I propose this solution, using a str_to_tbl function (see function definition after the proof of concept.) SQL> select * from gab ; USR VAL ---------- --------- GAP 1 GAP 5 GAP 7 GAP 9 JKL 8 JKL 5 XXX 1 XXX 5 8 ligne(s) sélectionnée(s). SQL> variable num_list varchar2 (4000) SQL> select b.usr 2 from 3 (select distinct a.usr, a.val from gab a) b, 4 (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d 5 where 6 b.usr = d.usr and 7 b.val in (select * 8 from 9 the (select 10 cast (str_to_tbl (:num_list) as my_number_table) 11 from dual 12 ) 13 ) 14 group by b.usr, d.num_usr_val 15 having 16 count(*) = d.num_usr_val 17 and count (*) = (select count (*) 18 from 19 the (select 20 cast (str_to_tbl (:num_list) as my_number_table) 21 from dual 22 ) 23 ) 24 SQL> execute :num_list := '1,5' Procédure PL/SQL terminée avec succès. SQL> / USR ---------- XXX SQL> execute :num_list := ' 8 , 5 ' Procédure PL/SQL terminée avec succès. SQL> / USR ---------- JKL SQL> execute :num_list := '1,5,7' Procédure PL/SQL terminée avec succès. SQL> / aucune ligne sélectionnée SQL> execute :num_list := '1,5,7,8' Procédure PL/SQL terminée avec succès. SQL> / aucune ligne sélectionnée SQL> execute :num_list := '1,5,7,9' Procédure PL/SQL terminée avec succès. SQL> / USR ---------- GAP SQL> execute :num_list := '1,5,7,8,9' Procédure PL/SQL terminée avec succès. SQL> / aucune ligne sélectionnée script: drop table gab; create table gab (usr varchar2(10) not null, val number not null) ; insert into gab (usr, val) values ('GAP', 1) ; insert into gab (usr, val) values ('GAP', 5) ; insert into gab (usr, val) values ('GAP', 7) ; insert into gab (usr, val) values ('GAP', 9) ; insert into gab (usr, val) values ('JKL', 8) ; insert into gab (usr, val) values ('JKL', 5) ; insert into gab (usr, val) values ('XXX', 1) ; insert into gab (usr, val) values ('XXX', 5) ; commit ; create or replace type my_number_table as table of number ; / create or replace function str_to_tbl (p_str in varchar2) return my_number_table as l_str varchar2 (32760) default p_str || ',' ; l_n number ; l_pos pls_integer default 1 ; l_data my_number_table := my_number_table () ; begin loop l_n := instr (l_str, ',', l_pos) ; exit when (nvl (l_n, 0) = 0) ; l_data.extend ; l_data (l_data.count) := ltrim (rtrim (substr (l_str, l_pos, l_n - l_pos))) ; l_pos := l_n + 1 ; end loop; return l_data ; end; / variable num_list varchar2 (4000) select b.usr from (select distinct a.usr, a.val from gab a) b, (select c.usr, count (*) as num_usr_val from gab c group by c.usr) d where b.usr = d.usr and b.val in (select * from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) group by b.usr, d.num_usr_val having count(*) = d.num_usr_val and count (*) = (select count (*) from the (select cast (str_to_tbl (:num_list) as my_number_table) from dual ) ) execute :num_list := '1,5' / execute :num_list := ' 8 , 5 ' / execute :num_list := '1,5,7' / execute :num_list := '1,5,7,8' / execute :num_list := '1,5,7,9' / execute :num_list := '1,5,7,8,9' / > -----Original Message----- > Vladimir Begun > > DROP TABLE gab; > CREATE TABLE gab (usr VARCHAR2(10) NOT NULL, val NUMBER NOT > NULL --, CONSTRAINT gab$uq UNIQUE (usr, val) > ); > INSERT INTO gab VALUES('GAP', 1); > INSERT INTO gab VALUES('GAP', 5); > INSERT INTO gab VALUES('GAP', 7); > INSERT INTO gab VALUES('JKL', 8); > INSERT INTO gab VALUES('JKL', 5); > COMMIT; > > SELECT usr > FROM ( > SELECT DISTINCT usr, val FROM gab > ) > WHERE val IN (1, 5, 7) > GROUP BY > usr > HAVING COUNT(*) = 3 -- number of elements in the list > / > > Depending on the existence of the constraint, here gab$uq, you can > either use inline view of run it against original table. > > Gabriel Aragon wrote: > > I have a table with 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).