Re: Silly SQL Question

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 17 Nov 2003 11:54:26 -0800
Message-ID: <F001.005D6F27.20031117115426@fatcity.com>

Using Bambi's table and values. Try this query, it's ugly but it works (kind of like the contributor...)

Daniel

  1 select o.usr, count(o.usr)
  2 from (select distinct usr, val

  3        from gab
  4        where val in (1,5,7)
  5          and usr not in (select usr
  6                          from gab
  7                          where val not in (1,5,7))) o

  8 group by o.usr
  9* having count(o.usr) = 3

Jacques Kilchoer wrote:

> The original question was "show me the users who have ALL the values in the list but NOT MORE than the values in the list."
>
> > -----Original Message-----
> > Bellow, Bambi
> >
> > 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.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jacques Kilchoer
> INET: Jacques.Kilchoer_at_quest.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.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 - 13:54:26 CST