Re: help with select statement
Date: Fri, 16 Jan 2009 04:24:44 -0800 (PST)
Message-ID: <d44a2927-62e8-45d4-83a9-0492316480b7_at_k1g2000prb.googlegroups.com>
On Jan 16, 12:32 am, wibni <Boettger.Andr..._at_gmail.com> wrote:
> Hi,
>
> 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
> cscontact.name
>
> 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:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/0bf1746f44d29ac0
(Follow up to the above which shows performance differences, some of
which are version dependent:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/6b51cb4e0c26256b
If you search the comp.databases.oracle.misc and comp.databases.oracle.server 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