RE: Sql question

From: Bellows, Bambi <BBellows_at_usg.com>
Date: Tue, 12 Feb 2002 10:26:02 -0800
Message-ID: <F001.0040D066.20020212095338@fatcity.com>

Why not try MINUS

Select x, y, z
>From tab
Where condition=1
Minus
Select x, y, z
>From tab
Where condition=2;

HTH,
Bambi.

-----Original Message-----
Sent: Tuesday, February 12, 2002 10:48 AM To: Multiple recipients of list ORACLE-L

Remco,

This is exactly what he wants - all records where the first one exists and the second one does not exist.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, February 12, 2002 10:23 AM To: Multiple recipients of list ORACLE-L

Thomas,

"NOT EXISTS" and "<>" equals "must be at least one"

Right ?

That's not what Zsolt wants ... :-)

-----Oorspronkelijk bericht-----
Van: Mercadante, Thomas F [mailto:Thomas.Mercadante_at_Labor.State.Ny.Us] Verzonden: dinsdag 12 februari 2002 14:28 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Sql question

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

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

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

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