Обсуждение: Join three tables and specify criteria... I know this should be easy!
Then I input data thus.
INSERT INTO user VALUES(1, 'Sandor');
INSERT INTO language VALUES ('EN', 'English');
INSERT INTO user_language VALUES(1, 'EN');
Now, I want the user who speaks English and the German - I may need to specify 3, 4
or conceivably even 5 languages.
I have done this, but I'm stuck :-)
this gives me
Give me the ids of all who speak 'EN' and 'DE' (or
possibly 'EN', 'DE', 'NL' and 'FR') for example.
>> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Paul Linehan
>> Sent: Friday, August 29, 2014 9:44 AM
>> To: Chuck Roberts
>> Cc: pgsql-novice@postgresql.org
>> Subject: [NOVICE] Join three tables and specify criteria... I know this should be easy!
>>
>>
>>
>> Now, I want the user who speaks English and the German - I may need to specify 3, 4
>> or conceivably even 5 languages.
>>
>> I have done this, but I'm stuck :-)
>> SELECT u.user_name, l.language_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
>>
SELECT DISTINCT u.user_id
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 u.user_language IN ('EN','DE')
(or)
WHERE "upper"(l.language_name) IN ('GERMAN','ENGLIGH')
See if this gives you what you want...
Ken Benson
>>
>> this gives me
>>
>> Sandor, German
>> Sandor, English
>> Gabor, English
>>
>> 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.
>> TIA and rgs,
>>
>> Paul...
>>
>>
I haveHi all, having a bit of a brain burp day! :-)CREATE TABLE user(user_id INTEGER PRIMARY KEY,user_name VARCHAR(25),);CREATE TABLE language(iso_code CHAR(2) PRIMARY KEY,language_name VARCHAR(30));CREATE TABLE user_language(ul_user_id INT,ul_iso_code CHAR(2),PRIMARY KEY (ul_user_id, ul_iso_code),CONSTRAINT ul_user_id_fk FOREIGN KEY (ul_user_id) REFERENCES user (user_id),CONSTRAINT ul_iso_code_fk FOREIGN KEY (ul_iso_code) REFERENCES language (iso_code));
Then I input data thus.
INSERT INTO user VALUES(1, 'Sandor');INSERT INTO user VALUES(2, 'Gabor');
INSERT INTO language VALUES ('EN', 'English');INSERT INTO language VALUES ('DE', 'German');
INSERT INTO user_language VALUES(1, 'EN');INSERT INTO user_language VALUES(1, 'DE');INSERT INTO user_language VALUES(2, 'EN');
Now, I want the user who speaks English and the German - I may need to specify 3, 4
or conceivably even 5 languages.
I have done this, but I'm stuck :-)SELECT u.user_name, l.language_nameFROM user uJOIN user_language ulON u.user_id = ul.ul_user_idJOIN language lON ul.ul_iso_code = l.iso_code
this gives meSandor, GermanSandor, EnglishGabor, EnglishI 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.TIA and rgs,Paul...
user table for each language spoken which would not
be very normalised (or elegant :-) ).
> SELECT DISTINCT u.user_id
> 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 u.user_language IN ('EN','DE')
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
Whoops:
Should be WHERE ul. iso_code IN ('EN','DE')
From: Paul Linehan [mailto:linehanp@tcd.ie]
Sent: Friday, August 29, 2014 10:08 AM
To: Ken Benson
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Join three tables and specify criteria... I know this should be easy!
2014-08-29 17:59 GMT+01:00 Ken Benson <Ken@infowerks.com>:
Hi Ken, and thanks for your input,
but there's no u.user_language field - user_language is a
joining table - otherwise, I'd have to have a record in the
user table for each language spoken which would not
be very normalised (or elegant :-) ).
Paul...
> SELECT DISTINCT u.user_id
> 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 u.user_language IN ('EN','DE')
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
> SELECT ul_user_id, user_name
> FROM user_language ul
> INNER JOIN language l ON ul.ul_iso_code = l.iso_code
> INNER JOIN user u ON u.user_id = ul.ul_user_id
> where l.language_name IN ('English', 'German')
> group by ul_user_id, user_name having COUNT(*) > 1
do if I wish to specify just 'EN' say, or just 'DE'? Sometimes one
language is sufficient and we only need people who speak one
2014-08-29 18:11 GMT+01:00 Ken Benson <Ken@infowerks.com>:
> Whoops:
> Should be WHERE ul. iso_code IN ('EN','DE')
SELECT DISTINCT u.user_id
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 IN ('EN','DE');
1
2
language required.
I thought the user_id is all you wanted …
But - you only want the id/name for a person that speaks ALL of the languages specified?
Or any user that speaks ANY of the specified languages?
Or Id/name/languages spoken?
What is the exact output you're looking for?
Says,
Ken
From: Paul Linehan [mailto:linehanp@tcd.ie]
Sent: Friday, August 29, 2014 10:42 AM
To: Ken Benson
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Join three tables and specify criteria... I know this should be easy!
Hi Ken and, again, thanks for your input,
2014-08-29 18:11 GMT+01:00 Ken Benson <Ken@infowerks.com>:
> Whoops:
> Should be WHERE ul. iso_code IN ('EN','DE')
but
SELECT DISTINCT u.user_id
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 IN ('EN','DE');
gives
user_id
1
2
And see also my reply to Naresh about if there's only one
language required.
Paul...
--
linehanp@tcd.ie
Mob: 00 353 86 864 5772
> I thought the user_id is all you wanted …
> But - you only want the id/name for a person that speaks ALL of the languages specified?
> Or any user that speaks ANY of the specified languages?
> Or Id/name/languages spoken?
> What is the exact output you're looking for?
in the the user table is trivial) of all users who speak
the specified language(s).
that I gave, then I want is Sandor's id (and name). If I specify only
there's only one (IN ('EN')) - i.e. Naresh's COUNT(*) > 1 won't
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
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
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.
Then I input data thus.
INSERT INTO user VALUES(1, 'Sandor');
INSERT INTO language VALUES ('EN', 'English');
INSERT INTO user_language VALUES(1, 'EN');
Now, I want the user who speaks English and the German - I may need to specify 3, 4
or conceivably even 5 languages.
I have done this, but I'm stuck :-)
this gives me
Give me the ids of all who speak 'EN' and 'DE' (or
possibly 'EN', 'DE', 'NL' and 'FR') for example.