help with select statement 1

[wysiwygger]

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 would be much appreciated.

 

[Dagon]

At times I feel like the world's number one advocate of analytic functions, since I seem to suggest them as a solution for virtually every problem. However, I do find them incredibly useful and think they can come up trumps for this one as well.

select distinct name, swcode 
from 
(select cscontact, relkeyword, swcode,
count(case when relkeyword.swcode = 173 then 1 end) over (partition by cscontact.name) as cnt173
from cscontact,relkeyword 
where cscontact.pubindex = relkeyword.relpubindex(+))
where cnt173 = 0
order by name

 

[wysiwygger]

Thanks Dagon!!!

After changing the second select, your statement runs fine. Excellent!

select distinct name, swcode
from
(select name, swcode,
count(case when relkeyword.swcode = 173 then 1 end) over (partition by cscontact.name) as cnt173
from cscontact,relkeyword
where cscontact.pubindex = relkeyword.relpubindex(+))
where cnt173 = 0
order by name

 

[SantaMufasa]

Thanks Dagon!!!...your statement runs fine. Excellent!

I'm certain that Dagon appreciates your "Thanks", but I've noticed that during your 2 1/2 years as a member of Tek-Tips and your two dozen threads for which you have asked for help from Tek-Tipsters, you have apparently not found the button at the lower left of each reply that says:

Thank <helper's name>
for this valuable post!

So, a deserved

is the "currency" by which we tender our "Thanks" here at Tek-Tips for a job well done.

Dagon certainly deserves a

for his excellent illustration of Oracle's Analytic Functions.

Please click on the screen button that gives a

to Dagon.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Dagon,

Apparently awarding well-deserved

s, even after a gentle reminder from others, is not in wysiwygger's code of ethics, so please accept one from me in recognition of your continued excellence in your responses.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”