-2

Trying to get a list of employees who have a particular set of certificates, e.g. BSCS and MSCS certificates.

Certificates Table

enter image description here

Employee Certificates

enter image description here

SELECT * FROM users AS u
JOIN employee_certificates e ON e.user_id = u.id
JOIN certificates c ON e.certificate_id = c.id
WHERE c.id = 9 AND c.id=10

Return Nothing But when I use

WHERE c.id IN (9,10)

Returning employees 206,207,208

Because they have these Certificates.

But what I want are the employees that have both BSCS and MSCS certificates.

Using this Query I can get result with Certificate id 9 and 10.. but how can i join user to get user detail.

Select certificate_id
from
(
Select distinct user_id, certificate_id
from
employee_certificates
where certificate_id in (9,10)
) a

group by certificate_id

i try this query but getting delicate result

 Select certificate_id,a.name
from
(
Select distinct user_id, certificate_id,u.name
from
employee_certificates
  JOIN users u ON u.id = employee_certificates.user_id
where certificate_id in (9,10)
) a
 
group by certificate_id,a.name

enter image description here

What is Right Result i'm excepting? return only 1 user who's user_id is 206 has both certificate (9,10)

Christian Abila
  • 74
  • 6
Fredericka Hartman
  • 835
  • 1
  • 7
  • 13
  • 3
    You asked [exactly the same question an hour ago](https://stackoverflow.com/questions/73269547/select-employee-who-has-multiple-certificate), and it was closed as a duplicate of a [past question](https://stackoverflow.com/questions/4047484/selecting-with-multiple-where-conditions-on-same-column). The right action for you would be learn from the linked answers and apply them to your case, not to delete your question and repost it. – Bill Karwin Aug 07 '22 at 19:03
  • There where clause is a logical expression. `(x=9) AND (x=10)` can never evaluate to true. When you give it a row with a 9 you get `9=9 AND 9=10` which evaluates as `TRUE AND FALSE` which evaluates as `FALSE`. – MatBailie Aug 07 '22 at 21:05

1 Answers1

2
   SELECT u.*
     FROM employee_certificates ec
     JOIN users u ON u.id = ec.user_id
    WHERE ec.certificate_id IN (9, 10)
 GROUP BY ec.user_id
   HAVING COUNT(ec.user_id) = 2;

In case of same certificate allowed multiple times (thanks matbailie):

   SELECT u.*
     FROM employee_certificates ec
     JOIN users u ON u.id = ec.user_id
    WHERE ec.certificate_id IN (9, 10)
 GROUP BY ec.user_id
   HAVING COUNT(DISTINCT ec.certificate_id) = 2;

Adapt the HAVING condition to the number of certificates to check for.

Cheers

Christian Abila
  • 74
  • 6