Re: help with select statement

From: Charles Hooper <>
Date: Fri, 16 Jan 2009 04:24:44 -0800 (PST)
Message-ID: <>

On Jan 16, 12:32 am, wibni <> wrote:
> Hi,
> I've got the following SQL statement and it is working almost fine.
> select distinct, 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 group which might help: (Follow up to the above which shows performance differences, some of which are version dependent:

If you search the and groups you will likely find other examples.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 16 2009 - 06:24:44 CST

Original text of this message