SQL query - double condition on relational table

Question

I'm trying to get all users that speak English and French based on the schema above. How can I achieve this?

I've tried with something like:

SELECT * FROM User
INNER JOIN UserLanguage on User.idUser = UserLanguage.idUser
INNER JOIN Language on UserLanguage.idLanguage = Language.idLanguage
WHERE Language.name = "FR" AND Language.name = "EN"

Answer 1

I'm using a subquery that counts the number of languages (that are either FR or EN) spoken by each user. It then returns all the id of all users that speaks both of these languages. The outer query returns all columns for each of these users:

SELECT Users.*
FROM Users
WHERE idUser IN (
  SELECT UserLanguage.idUser
  FROM
    UserLanguage INNER JOIN Language
    ON UserLanguage.idLanguage = Language.idLanguage
  WHERE
    Language.name IN ("FR", "EN")
  GROUP BY
    UserLanguage.idUser
  HAVING
    COUNT(DISTINCT Language.name)=2
)

Answer 2

Change your conditional from

WHERE Language.name = "FR" AND Language.name = "EN"

to

WHERE (Language.name = "FR" OR Language.name = "EN")

You should never have an entry in the database that has two values for a single field, but if you use the "OR" operator, you should be selecting the entry if either value is equal.