From: | Paul Linehan <linehanp(at)tcd(dot)ie> |
---|---|
To: | Chuck Roberts <croberts(at)gilsongraphics(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Join three tables and specify criteria... I know this should be easy! |
Date: | 2014-08-29 16:43:37 |
Message-ID: | CAF4RT5R43BgEFDnSTpLe1zGpmk5VEWYQE5YrtxAJkwhvtFQ+mA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)tcd(dot)ie
Mob: 00 353 86 864 5772
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Benson | 2014-08-29 16:59:38 | Re: Join three tables and specify criteria... I know this should be easy! |
Previous Message | Chuck Roberts | 2014-08-29 10:06:53 | Need GUI tool to create complex queries |