SQL use of conditional AND on a join table to ensure at least two or multiple values are matched

Question

I have a join table named languages_services that basically joins the table services and languages.

I need to find a service that is able to 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 data provided above, I want to test for language_id=1 AND language_id=2 where the result would look like this

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

Obviously it doesn't return the one with service_id=2 because it doesn't service Espanol.

Any tips on this is greatly appreciated!

MatBailie · Accepted Answer · 2012-03-16T14:10:34.210

5

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're always looking at 2 languages you could do it with joins, but the aggregate version is easier to adapt to differing numbers of language_ids. (Add an OR, or add an item to the IN list, and change the COUNT(*) = 2 to COUNT(*) = 3, etc, etc).

Be aware, however, that this scales very poorly. And with this table structure there isn't much you can do about that.

EDIT Example using a join for 2 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

edited Mar 16 '12 at 14:10

answered Mar 16 '12 at 13:57

MatBailie

83,401
18
103
137

Awesome, that worked! Thanks. If possible, could you also show how that works in a join table for two language, just to get an idea? That'd be greatly appreciated, thanks. – chadwtaylor Mar 16 '12 at 14:04
@chadwtaylor - `JOIN` version added for exactly 2 languages – MatBailie Mar 16 '12 at 14:10
You're awesome @Dems for the quick responses! I can now see why you said the first two examples are better in terms to aggregating multiple languages vs the join. – chadwtaylor Mar 16 '12 at 14:14
@Dems should that not be COUNT(DISTINCT lanaguage_id) instead of COUNT(*) in case the table allows for multiple entries of the same combination of service_id and lanaguage_id? – My Other Me Mar 16 '12 at 14:50
@MyOtherMe - If duplicates are allowed, yes. But I'd hope that a mapping table such as this has the two fields as a primary (and so unique) key. If I'm wrong on that assumption, then your fix is absolutely correct :) – MatBailie Mar 16 '12 at 14:56

SQL use of conditional AND on a join table to ensure at least two or multiple values are matched

1 Answers1