Need help with SQL query

Question

Current query

SELECT *
FROM employee AS E
INNER JOIN credential AS C ON (C.id = E.credentialId)
LEFT JOIN person AS P ON (C.personId = P.id)

I want to modify this query so that it I just SELECT the employees/credentials which have both loginroles 1 and 2 (loginrole.id).

Relevant tables

loginrole
    id
    name
credential_has_loginrole
    id
    credentialId
    loginroleId

Answer 1

You can join a subquery, which will return the credential Id's that have both login roles.

SELECT E.*, C.*, P.*
FROM employee AS E
  INNER JOIN (
      SELECT credentialId
      FROM credential_has_loginrole
      WHERE loginroleId IN (1,2)
      GROUP BY credentialId
      HAVING COUNT(DISTINCT loginroleId) = 2
    ) g ON E.credentialId = g.credentialId
  INNER JOIN credential AS C ON (C.id = E.credentialId)
  LEFT JOIN person AS P ON (C.personId = P.id)

Update: As per comments, to find employees with either or:

SELECT E.*, C.*, P.*
FROM employee AS E
  INNER JOIN (
      SELECT credentialId
      FROM credential_has_loginrole
      WHERE loginroleId IN (1,2)
      GROUP BY credentialId
    ) g ON E.credentialId = g.credentialId
  INNER JOIN credential AS C ON (C.id = E.credentialId)
  LEFT JOIN person AS P ON (C.personId = P.id)

Alternatively, you can use a JOIN together with DISTINCT:

SELECT DISTINCT E.*, C.*, P.*
FROM employee AS E
  INNER JOIN credential AS C ON (C.id = E.credentialId)
  INNER JOIN credential_has_loginrole chr
    ON E.credentialId = chr.credentialId
      AND chr.loginroleId IN (1,2)
  LEFT JOIN person AS P ON (C.personId = P.id)

Answer 2

The Scrum Meister's answer is correct. You can alternatively use 2 join trees:

    SELECT *
    FROM employee AS E
    INNER JOIN credential AS c 
        INNER JOIN credential_has_loginrole chl0 ON (c0.id=chl0.credentialId AND chl0.loginroleId=1)
        INNER JOIN credential_has_loginrole chl1 ON (c0.id=chl1.credentialId AND chl1.loginroleId=2)
    ON (c.id = E.credentialId)