Using SQL conditional AND in the join table to match two or more values

Question

I have a connection table called languages_services , which basically joins the services and languages table.

I need to find a service that can serve both ENGLISH (language_id=1) and ESPANOL (language_id=2) .

 table languages_services ------------------------ service_id | language_id ------------------------ 1 | 1 1 | 2 1 | 3 2 | 1 2 | 3 

With the above data, I want to check for language_id=1 AND language_id=2 , where the result will look like this:

 QUERY RESULT ------------ service_id ------------ 1 

Obviously, it does not return a value with service_id = 2, because it does not serve Espanol.

Any advice on this is greatly appreciated!

Answer 1

 SELECT service_id FROM language_services WHERE language_id = 1 OR language_id = 2 GROUP BY service_id HAVING COUNT(*) = 2 

Or...

 WHERE lanaguage_id IN (1,2) GROUP BY service_id HAVING COUNT(*) = 2 

If you always look at 2 languages, you can do this with unions, but the consolidated version is easier to adapt to different numbers of language_ids. (Add OR or add an item to the IN list and change COUNT(*) = 2 to COUNT(*) = 3 , etc. Etc.).

Remember, however, that this is very small. And with this table structure, you can't do anything about it.

EDIT Connection example for two languages

 SELECT lang1.service_id FROM language_services AS lang1 INNER JOIN language_services AS lang2 ON lang1.service_id = lang2.service_id WHERE lang1.language_id = 1 AND lang2.language_id = 2