Re: Silly SQL Question

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Thu, 13 Nov 2003 14:49:24 -0800
Message-ID: <F001.005D698E.20031113144924@fatcity.com>

Gabriel

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.

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

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.

> 

> TIA

> Gabriel



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.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 Thu Nov 13 2003 - 16:49:24 CST