RE: Sql question

From: Daemen, Remco <Remco.Daemen_at_werklinq.nl>
Date: Tue, 12 Feb 2002 03:45:28 -0800
Message-ID: <F001.0040C651.20020212020327@fatcity.com>

You have to realize that you need two occurences of table c, as you want to match values of different records. You can do this in various different ways. One of them is something like: 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 not exists
( select ''
  from c c_2
  where c_2.b1=b.b1
  and c_2.b2=b.b2
  and c.searchvalue='second one'
);

HTH, Remco

-----Oorspronkelijk bericht-----
Van: Csillag Zsolt [mailto:starsoft_at_interware.hu] Verzonden: maandag 11 februari 2002 22:40 Aan: Multiple recipients of list ORACLE-L Onderwerp: Sql question

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: Daemen, Remco
  INET: Remco.Daemen_at_werklinq.nl

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

Received on Tue Feb 12 2002 - 05:45:28 CST