Re: help with select statement
Date: Fri, 16 Jan 2009 04:24:44 -0800 (PST)
On Jan 16, 12:32 am, wibni <Boettger.Andr..._at_gmail.com> wrote:
> I've got the following SQL statement and it is working almost fine.
> select distinct cscontact.name, relkeyword.swcode from cscontact,
> relkeyword where cscontact.pubindex = relkeyword.relpubindex(+) AND
> (relkeyword.swcode <> 173 OR relkeyword.swcode IS NULL) order by
> The statement returns all contacts which have no keyword 173 or no
> keyword at all assigned to it.
> Unfortunately the table relkeyword has more then 1 entry for the same
> contact person.
> 1 person might have an entry with keyword 173 and another one with
> keyword 99.
> Currently the entry with keyword 173 is left out but it still shows
> the line with keyword 99.
> I want to leave out a contact completely as soon as it has keyword 173
> assigned to it.
> Any ideas on how to achieve that?
In agreement with Gazzag. When posting to this group with a request for help, please provide those three items, as well as showing what you have tried so far.
Suggestions for your SQL problem:
* NOT IN syntax and NVL(relkeyword.swcode,172) * NOT EXISTS syntax and NVL(relkeyword.swcode,172) * outer join syntax and NVL(relkeyword.swcode,172) * MINUS syntax and NVL(relkeyword.swcode,172)
Old posts from the comp.databases.oracle.misc group which might help:
(Follow up to the above which shows performance differences, some of
which are version dependent:
If you search the comp.databases.oracle.misc and comp.databases.oracle.server groups you will likely find other examples.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 16 2009 - 06:24:44 CST