Re: help with select statement
Date: Fri, 16 Jan 2009 04:12:17 -0800 (PST)
On Jan 16, 12:32 pm, 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?
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
Rudi Kristanto. Received on Fri Jan 16 2009 - 06:12:17 CST