Users in my website must be able to select languages they know (English, German, etc), so I have a table languages
with 150 languages (id, name) and table users
(id and a lot of other fields).
Website's visitors must be able to search users by their language knowledge. For example, they might be looking for someone good at English, German and French.
I'm unable to solve this. If I make a table language_knowledge
with fields id, language_id and user_id, then I will be able to find users who know one specific language (lets say English), but I need to find users who know, for example, BOTH English and German.
How do I do this?
Best Answer
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!.
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.
The
HAVING COUNT(*) = 2
clause limits the results to only people who know both two languages. If theIN (...)
clause had 3 different languages, you'd need to modify theHAVING
clause to reflect that, like:HAVING COUNT(*) = 3