2

I've a table we have userids 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
.................

Nands
  • 1,541
  • 2
  • 20
  • 33

4 Answers4

8

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
Community
  • 1
  • 1
Vincent Mimoun-Prat
  • 28,208
  • 16
  • 81
  • 124
  • +1 for the group-having solution - does the same as what I suggested, but with a simpler query :) – mingos May 19 '11 at 10:41
  • Thanks marvin. It works great. I couldn't understand why >=4 has been added at the end and I suppose the check should have been for =4 only as only 4 languages are being checked for? Could you elaborate this a bit. thanks – Nands May 19 '11 at 11:48
  • I've been waiting about 10 years for a built-in GROUP_CONCAT aggregate function in SQL Server. Still waiting. +1 btw, great answer. – Chris Fulstow May 19 '11 at 12:34
1

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;
mingos
  • 23,778
  • 12
  • 70
  • 107
  • It is - the only thing that's still a bit shaky about it is that you have to know how many languages you're looking for. It's usually not a problem if you use PHP or another server side language to construct the query, but then again, who knows - the OP isn't specific about this :). – mingos May 19 '11 at 10:36
  • Could you load the languages into a table variable @Languages, then do `WHERE language IN (SELECT * FROM @Languages)` and have `declare @Count = (SELECT COUNT(*) FROM @Languages)` and `HAVING cnt = @Count`? – Chris Fulstow May 19 '11 at 10:40
  • Aye, `having` is the way to go :) – mingos May 19 '11 at 10:42
0

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')
Chris Fulstow
  • 41,170
  • 10
  • 86
  • 110
0

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)
pratik garg
  • 3,282
  • 1
  • 17
  • 21