Join three tables and specify criteria... I know this should be easy! - Mailing list pgsql-novice

From Paul Linehan
Subject Join three tables and specify criteria... I know this should be easy!
Date
Msg-id CAF4RT5R43BgEFDnSTpLe1zGpmk5VEWYQE5YrtxAJkwhvtFQ+mA@mail.gmail.com
Whole thread Raw
Responses Re: Join three tables and specify criteria... I know this should be easy!  (Ken Benson <Ken@infowerks.com>)
Re: Join three tables and specify criteria... I know this should be easy!  (Naresh Kumar <narain337@gmail.com>)
Re: Join three tables and specify criteria... I know this should be easy!  (Kevin Grittner <kgrittn@ymail.com>)
Re: Join three tables and specify criteria... I know this should be easy!  ("Luiz Matsumura" <luiz@planit.com.br>)
List pgsql-novice


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

pgsql-novice by date:

Previous
From: Chuck Roberts
Date:
Subject: Need GUI tool to create complex queries
Next
From: Ken Benson
Date:
Subject: Re: Join three tables and specify criteria... I know this should be easy!