Query for SELECT all users who speak all the specified languages

I've a table we have userid开发者_运维问答s of people and the langauges they can speak. just two columns, userid and language.

I want to find all those users who can speak hindi AND english AND german AND french. How should I write this query ? I cannot use Inner Join 4 times. Problem being the check for number of languages might increase, I might want to check for more languages.

userid | language

1 | english

4 | english

1 | french

1 | german

.................

If using MySQL, you could do something like (to be debugged, not tested):

SELECT userid FROM (
    SELECT userid, GROUP_CONCAT(language SEPARATOR ',') AS languages
    FROM UserLanguage 
    ORDER BY userid ASC, language ASC 
    GROUP BY userid)
WHERE languages LIKE '%english%french%german%hindi%';

(the languages in the LIKE clause have to be sorted)

How to use GROUP BY to concatenate strings in MySQL?

Or maybe faster:

SELECT userid 
FROM UserLanguage 
WHERE language IN ('fr', 'en, 'de', 'hi') 
GROUP BY userid 
HAVING COUNT(DISTINCT(language)) >= 4

You can use a subquery:

SELECT userid
FROM (
    SELECT userid, COUNT(*) AS cnt
    FROM mytable
    WHERE language IN ('hindi','german','french','english')
    GROUP BY userid
) AS t
WHERE cnt = 4;

You could use a correlated subquery, like this:

SELECT UserId
FROM UserLanguage UL1
WHERE
    EXISTS (SELECT * FROM UserLanguage UL2 WHERE UL2.UserId = UL1.UserId
        AND UL2.Language = 'english') AND
    EXISTS (SELECT * FROM UserLanguage UL2 WHERE UL2.UserId = UL1.UserId
        AND UL2.Language = 'hindi') AND
    EXISTS (SELECT * FROM UserLanguage UL2 WHERE UL2.UserId = UL1.UserId
        AND UL2.Language = 'german') AND
    EXISTS (SELECT * FROM UserLanguage UL2 WHERE UL2.UserId = UL1.UserId
        AND UL2.Language = 'french')

I think as you don't have count and name of language then you should try following query...

select userid 
from UserLanguage 
group by userid 
having count(language)= (select count(distinct language) from userid)