Which is MUCH cleaner than my next proposed solution:
SELECT DISTINCT u.user_id,u.user_name
FROM user u
JOIN user_language ul
ON u.user_id = ul.ul_user_id
JOIN language l
ON ul.ul_iso_code = l.iso_code
WHERE ul.ul_iso_code = 'EN'
AND ul.ul_iso_code = 'DE';
Writes,
Ken Benson
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On
> Behalf Of Kevin Grittner
> Sent: Friday, August 29, 2014 11:13 AM
> To: Paul Linehan; Chuck Roberts
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Join three tables and specify criteria... I know this should be easy!
>
> Paul Linehan <linehanp@tcd.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
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your
> subscription:
> http://www.postgresql.org/mailpref/pgsql-novice