This is a classic situation where you use a joining table (a fiddle based on this can be found here. It was done much later than the original answer - better candidates for PRIMARY KEY of the language table!.
CREATE TABLE user
PRIMARY KEY (user_id)
CREATE TABLE language
iso_code CHAR(2), -- or whatever you want the PRIMARY KEY to be.
PRIMARY KEY (iso_code)
CREATE TABLE user_language
PRIMARY KEY (ul_user_id, ul_iso_code),
FOREIGN KEY (ul_user_id) REFERENCES user (user_id),
FOREIGN KEY (ul_iso_code) REFERENCES language (iso_code)
I forgot to mention that the FOREIGN KEYs must point to a PRIMARY KEY or a UNIQUE KEY (and not some other arbitrary field) in the referenced table.
And for completeness, you should put a PRIMARY KEY on your two joining fields. This will help query response time and avoid multiple entries of the same data. Whatever your app does, always try to enforce RI (Referential Integrity) in the database.
This query shows how to get the user id and name for anyone who knows both English and German.
FROM user_language ul
INNER JOIN language l ON ul.ul_iso_code = l.iso_code
INNER JOIN user u ON u.user_id = ul.ul_user_id
WHERE l.language_name IN (
GROUP BY ul_user_id, user_name
HAVING COUNT(*) = 2;
HAVING COUNT(*) = 2 clause limits the results to only people who know both two languages. If the
IN (...) clause had 3 different languages, you'd need to modify the
HAVING clause to reflect that, like:
HAVING COUNT(*) = 3