Re: Join three tables and specify criteria... I know this should be easy!

Paul Linehan <linehanp(at)tcd(dot)ie> wrote:
> I really want Sandor's id - that's all that really counts.

>
> Give me the ids of all who speak 'EN' and 'DE' (or
> possibly 'EN', 'DE', 'NL' and 'FR') for example.

WITH required_languages(iso_code) AS (VALUES ('EN'),('DE'))
SELECT u.user_id, u.user_name
  FROM (
          SELECT ul.ul_user_id, count(*) cnt
            FROM required_languages r
            JOIN user_language ul on (ul.ul_iso_code = r.iso_code)
            GROUP BY ul.ul_user_id
            HAVING count(*) >= (SELECT count(*) FROM required_languages r2)
       ) x
  JOIN "user" u ON (u.user_id = x.ul_user_id);

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company