Home > mailing lists

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

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

From

Paul Linehan

Date:

29 August 2014, 19:43:43

Hi all, having a bit of a brain burp day! :-)

I have

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_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

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...

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

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

From

Ken Benson

Date:

29 August 2014, 19:59:52

>> 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...

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

From

Naresh Kumar

Date:

29 August 2014, 20:05:40

Hi Paul,

Try this, it should work

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

-Naresh

On Fri, Aug 29, 2014 at 11:43 AM, Paul Linehan <linehanp@tcd.ie> wrote:

Hi all, having a bit of a brain burp day! :-)

I have

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_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

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...

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

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

From

Paul Linehan

Date:

29 August 2014, 20:08:01

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

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

From

Ken Benson

Date:

29 August 2014, 20:12:00

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...

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

From

Paul Linehan

Date:

29 August 2014, 20:35:48

2014-08-29 18:05 GMT+01:00 Naresh Kumar <narain337@gmail.com>:

Hi Naresh, and thanks for that,

> 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

This gives the correct answer - however, I am just wondering what will I
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

language - your query won't pick them up. Any ideas?

(My apologies BTW for not having included that in my original

spec :-) ).

Paul...

> -Naresh

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

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

From

Paul Linehan

Date:

29 August 2014, 20:41:37

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

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

From

Ken Benson

Date:

29 August 2014, 20:49:03

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

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

From

Paul Linehan

Date:

29 August 2014, 21:09:21

Hi Ken,

> 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?

My apologies for not having been totally clear from

the outset.

What I would like is the user_id (user_name also being
in the the user table is trivial) of all users who speak
the specified language(s).

I.e. if the request is for 'EN' and 'DE', then in the SQL DDL and DML
that I gave, then I want is Sandor's id (and name). If I specify only

English, the it should give me Sandor's and Gabor's id (and name).

For the langauge or languages input (IN ('EN', 'DE'...)) - even if
there's only one (IN ('EN')) - i.e. Naresh's COUNT(*) > 1 won't

be correct, I would like the id (and name) of those who speak

that/those language(s).

I hope that this clarifies the situation?

Paul...

--

linehanp@tcd.ie

Mob: 00 353 86 864 5772

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

From

Kevin Grittner

Date:

29 August 2014, 21:13:10

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

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

From

Ken Benson

Date:

29 August 2014, 21:42:26

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

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

From

David G Johnston

Date:

30 August 2014, 03:34:45

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.

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

From

"Luiz Matsumura"

Date:

04 September 2014, 02:08:10

From: Paul Linehan

Sent: Friday, August 29, 2014 1:43 PM

To: Chuck Roberts

Cc: pgsql-novice@postgresql.org

Subject: [NOVICE] Join three tables and specify criteria... I know this should be easy!

Hi all, having a bit of a brain burp day! :-)

I have

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_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

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,

May be something like bellow can help you ?

SELECT ul.ul_user_id

FROM user_language ul

JOIN ( select ul_user_id from user_language ul WHERE ul.ul_iso_code = 'EN' ) t1 ON t1.ul_user_id = ul.ul_user_id

WHERE ul.ul_iso_code = 'DE'