1

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?

2 Answers 2

5

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
(
  user_id INT,
  user_name VARCHAR(25),
  PRIMARY KEY (user_id)
);

CREATE TABLE language
(
  iso_code CHAR(2),  -- or whatever you want the PRIMARY KEY to be.
  language_name VARCHAR(30),
  PRIMARY KEY (iso_code)
);

CREATE TABLE user_language
(
  ul_user_id INT,
  ul_iso_code CHAR(2),
  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.

SELECT ul_user_id
    , user_name
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 (
    'English'
    , 'German'
    )
GROUP BY ul_user_id, user_name 
HAVING COUNT(*) = 2;

The 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

3
  • In user_language I might have rows (1, EN), (1, DE), (2, DE). What the SQL would be to find a user who knows both EN and DE (in this case it's the user with id 1)? I don't see how this helps me with this problem. Aug 29, 2014 at 9:54
  • You basically wrote everything I already had in my question, but didn't answer the main question - how do I get users who know BOTH language1 and language2 ? Aug 29, 2014 at 10:17
  • I think that the question has been answered - see the fiddle - it clearly provides those who speak both German and English and not those who don't speak both - QED!
    – Vérace
    Apr 22, 2019 at 19:30
2

Building on the answer from @Vérace you can get your usernames like so:

Select 
    User_Name 
from dbo.user
WHERE EXISTS
    (SELECT ul_user_id
     FROM dbo.user_language
     WHERE user_id = 'USERID' and ul_iso_code = 'LANGUAGECODE'
    )
     AND EXISTS
    (SELECT ul_user_id
     FROM dbo.user_language
     WHERE user_id = 'USERID' and ul_iso_code = 'LANGUAGECODE' 
    )    

There are probably better solutions out there, but this might get you on your way.

2
  • Well, it's a starting point, but it's ugly. There must be an elegant solution for this. To avoid "monster" queries like this one. Aug 29, 2014 at 11:55
  • Correlated sub-query is the way to go as far as clarity of code is concerned, but are your sub-queries correlated? Shouldn't it be more like: from dbo.user A and WHERE user_id = A.user_id ?
    – Joel Brown
    Aug 30, 2014 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.