Need a query for many to many relationship in MYSQL

Question

Below is the table structure:

I have three tables : employee, skill and employee_skills.

Employee: id, firstname, lastname, etc.....
Skill : id, title, description 
Emplyoee_skills : id, employee_id (FK of employee table), skill_id(FK of skill table)

---

Now, I want the below output:

---

Employee
Id    firstname    lastname
1     Rajnikant    Patel
2     Steve        Jobs
3     Sachin       Tendulkar
4     Ratan        Tata

---

Skill
Id    title        description
1     java         java
2     mongodb      mongodb
3     PHP          PHP
4     spring       Spring framework 

---

Employee_skills
Id    employee_id   skill_id
1     1             1
2     1             2
3     2             1
4     3             2

---

So I want the query which can return the employee records who have the skills that are passed:

Let's say, I pass in where clause : s.title in ('mongodb', 'java'), then it should return record:

Id   firstName        lastName
 1   Rajnikant        Patel

Because this employee has both the skills.

Answer 1

Below query will give you desired results.

SELECT e.id, e.firstname, e.lastname
FROM Employee e
JOIN Employee_skills es
ON es.employee_id = e.id
JOIN Skill s
ON es.skill_id = s.id
WHERE s.title IN ('mongodb', 'java')
GROUP BY e.id
HAVING count(s.id) = 2

Answer 2

Here is how to do it: Select all employee skills for the desired skills, then only keep employees having the full count.

select *
from employee
where id in
(
  select employee_id 
  from employee_skills
  where skill_id in
  (
    select id 
    from skill
    where title in ('java', 'mongodb')
  )
  group by employee_id 
  having count(distinct skill_id) = 2
);

When adding another skill, you must check for a count of 3, of course, etc.