Mysql complicated Inner join scenario

user_721899 0
Viewed 23

The table contains employees and the clients they worked for.

employee client
a 1
a 4
b 2
c 1
c 2
d 3

I need to find a employee who worked only for one client which is particularity client=2 therefore result should be "b,2", because "b" and "d" are only employees who worked for one client but "b" should be expected because he worked for client=2 and "d" not

expected Result:

employee client
b 2

Result should not contain "c" , as "c" worked for client=2 but also for another client=1

I tried following query , it returns all employee who worked only for one client(any of all ) but not sure how to impose client should be "2"

SELECT COUNT(*),employee FROM employee_client GROUP BY emplyoee HAVING COUNT(*) = 1;
inner-join group-by mysql
1 Answers

You need conditional aggregation in the HAVING clause:

SELECT employee, MAX(client) client 
FROM employee_client 
GROUP BY employee 
HAVING COUNT(*) = 1 AND SUM(client <> 2) = 0;

See the demo.

user_104988280
Related