From: | Naresh Kumar <narain337(at)gmail(dot)com> |
---|---|
To: | Paul Linehan <linehanp(at)tcd(dot)ie> |
Cc: | Chuck Roberts <croberts(at)gilsongraphics(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Join three tables and specify criteria... I know this should be easy! |
Date: | 2014-08-29 17:05:11 |
Message-ID: | CAHuWDLiXUEMJ_Xp6MN8SbAG9WwzVZrNkECcxd1M7bveSisYfpg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)tcd(dot)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(at)tcd(dot)ie
>
> Mob: 00 353 86 864 5772
>
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Linehan | 2014-08-29 17:07:56 | Re: Join three tables and specify criteria... I know this should be easy! |
Previous Message | Ken Benson | 2014-08-29 16:59:38 | Re: Join three tables and specify criteria... I know this should be easy! |