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:[EMAIL PROTECTED]] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
