RE: Sql question

From: Mercadante, Thomas F <Thomas.Mercadante_at_Labor.State.Ny.Us>
Date: Tue, 12 Feb 2002 05:39:43 -0800
Message-ID: <F001.0040C7D1.20020212052821@fatcity.com>

Zsolt,

Try:

select a.something
,c.searchvalue

from a,

        b,
        c
  where a.a= b.a
        and b.b1= c.b1
        and b.b2= c.b2
        and c.searchvalue= 'first one' and
        and not exists(select 1 from c c1
                         where c1.b1 = c.b1
                         and   c1.b2 = c.b2
                         and   c1.searchvalue<> 'second one')

the above presumes that the columns b1 and b2 are part of the identifying columns for the c table.

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, February 11, 2002 4:40 PM
To: Multiple recipients of list ORACLE-L

Hi,

I have the following sql:

select a.something
,c.searchvalue

from a,

        b,
        c

where a.a= b.a

        and b.b1= c.b1
        and b.b2= c.b2

        and c.searchvalue= 'first one' and
        c.searchvalue<> 'second one'

The problem is that if a company has a record with c.searchvalue= 'first one' then the
query above list it although it has another record with c.searchvalue= 'second one'

To be more precise : I need to get the companies that have searchvalue = 'first one' but I don't want
to see companies that has 'second one'. (the main problem is with companies that have both values)

Thank you

Zsolt Csillag,
Hungary

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Csillag Zsolt
INET: starsoft_at_interware.hu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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.com
--

Author: Mercadante, Thomas F
INET: Thomas.Mercadante_at_Labor.State.Ny.Us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------