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:


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:

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



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:

 

 

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

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

From
Paul Linehan
Date:



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:



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:

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:

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:
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:
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:
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:
Sent: Friday, August 29, 2014 1:43 PM
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'