Home » SQL & PL/SQL » SQL & PL/SQL » Help with simple(??) query on two columns
Help with simple(??) query on two columns [message #195252] Wed, 27 September 2006 11:11 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi,

I have a table called clntrespons that has two columns:

clientcode, employeeid

client1 emp1
client1 emp2
client1 emp3
client1 emp4
client2 emp1
client2 emp6
client3 emp2


I need to produce a query that displays all clients that do not have either emp1 and/or emp2 and/or emp3
associated with them.
so if I were to query the above table, I would see client2 and client 3 displayed, as they do not have ALL 3 employees.
does this make sense?

thanks in advance,
Matt





Re: Help with simple(??) query on two columns [message #195256 is a reply to message #195252] Wed, 27 September 2006 11:31 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select clientcode,
       count(case when 
            employeeid in ('emp1', 'emp2', 'emp3') 
            then 1 else null end)
      from emp
	   group by clientcode
having count(case when employeeid 
             in ('emp1', 'emp2', 'emp3') 
             then 1 else null end) = 3
Re: Help with simple(??) query on two columns [message #195275 is a reply to message #195252] Wed, 27 September 2006 13:24 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Cthulhu...I believe your query should be changed to have count not equal to 3:

having count(case when employeeid 
             in ('emp1', 'emp2', 'emp3') 
             then 1 else null end) != 3


matpj, if the combinatin of clientcode & employeeid are unique, then you can remove the group by in my query below:

select clientcode, employeeid from (
	select clientcode, employeeid,
	count(*) over (parition by clientcode) cnt
	from client
	where employeeid in ('emp1','emp2','emp3')
	group by clientcode, employeeid)
where cnt != 3;

[Updated on: Wed, 27 September 2006 13:48]

Report message to a moderator

Previous Topic: Getting wrong result on summing
Next Topic: Getting error
Goto Forum:
  


Current Time: Fri Dec 09 02:20:38 CST 2016

Total time taken to generate the page: 0.07103 seconds