Re: help with select statement

From: <rudiathome_at_gmail.com>
Date: Fri, 16 Jan 2009 04:12:17 -0800 (PST)
Message-ID: <9aad8f2f-3941-4d64-883d-8589323b0a0d_at_r37g2000prr.googlegroups.com>

On Jan 16, 12:32 pm, 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?

this one might be sub optimal because it need scan relkeyword table twice.

select distinct cscontact.name, relkeyword.swcode from cscontact, relkeyword where cscontact.pubindex = relkeyword.relpubindex(+) AND (relkeyword.swcode <> 173 OR relkeyword.swcode IS NULL) and cscontact.pubindex not in

        (select relpubindex
         from relkeyword
         where relkeyword.swcode = 173)

order by cscontact.name

Regards,
Rudi Kristanto. Received on Fri Jan 16 2009 - 06:12:17 CST