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

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(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On
> Behalf Of Kevin Grittner
> Sent: Friday, August 29, 2014 11:13 AM
> To: Paul Linehan; Chuck Roberts
> Cc: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] 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
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org) To make changes to your
> subscription:
> http://www.postgresql.org/mailpref/pgsql-novice