Join three tables and specify criteria... I know this should be easy!
| От | Paul Linehan |
|---|---|
| Тема | Join three tables and specify criteria... I know this should be easy! |
| Дата | |
| Msg-id | CAF4RT5R43BgEFDnSTpLe1zGpmk5VEWYQE5YrtxAJkwhvtFQ+mA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Join three tables and specify criteria... I know this
should be easy!
Re: Join three tables and specify criteria... I know this should be easy! Re: Join three tables and specify criteria... I know this should be easy! Re: Join three tables and specify criteria... I know this should be easy! |
| Список | pgsql-novice |
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.
Give me the ids of all who speak 'EN' and 'DE' (or
possibly 'EN', 'DE', 'NL' and 'FR') for example.
TIA and rgs,
Paul...
В списке pgsql-novice по дате отправления: