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

From: Paul Linehan
Sent: Friday, August 29, 2014 1:43 PM
To: Chuck Roberts
Cc: pgsql-novice(at)postgresql(dot)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'