From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Join three tables and specify criteria... I know this should be easy! |
Date: | 2014-08-30 00:34:32 |
Message-ID: | 1409358872050-5816992.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Kevin Grittner-5 wrote
> Paul Linehan <
> linehanp@
> > 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);
The following is also generally useful:
iso_code = ALL(regexp_split_to_array(?::text,';'))
Where you can then pass in a string like:
'DE;EN'
Via your client library parameterized query capability (or psql variable)
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Join-three-tables-and-specify-criteria-I-know-this-should-be-easy-tp5816921p5816992.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2014-08-30 01:00:22 | Re: logical fields with defaults |
Previous Message | Jude DaShiell | 2014-08-29 19:18:35 | Re: showing weekdays of dates |