Thread: Possible use of a subselect?

Greetings All,

I've run into a spot where I think I could use a subselect but I'm not sure
how I would (or if I can).  This is a simple table which holds strings and
their translated counterparts.

Given the table structure:
string
------
id serial int4
stringid int4 not null
language varchar(32)
content varchar(255)

stringid would always equal 0 for the English version of any string.
Otherwise, it points the string.id of the English version.  Content contains
the string (or the translated version).  Language simple contains "English"
or "Chinese".

Everything is working great.  I'm trying to get a query that will return the
English version of every string ("SELECT id,content FROM STRING WHERE
language='English' and stringid=0") and their translated counterpart (say,
Korean) which would be ("SELECT content FROM string WHERE
stringid=ID.OF.ENGLISH.VERSION").

Ending up with:
| English.StringID | English String | Korean String |
-----------------------------------------------------
| 1                | Hello          | Whatever      |
| 2                | Goodbye        | NULL
         | -----------------------------------------------------
(NULL meaning that it hasn't been translated)

I could select all English strings, then select the Korean versions in a
loop but being new to postgres I thought their might be a better way?

Thanks in advance for any help,
Adam

No subselect neccessary:

create table trans (id serial primary key,
                    stringid int not null,
                    lang varchar(32),
                    content varchar(255));

insert into trans (stringid, lang, content) values (0,'English','Hello');
insert into trans (stringid, lang, content) values (1,'Spanish','Hola');
insert into trans (stringid, lang, content) values
(0,'English','Wassup'); -- no translation

          select eng.stringid,
                 eng.content,
                 span.content
            from trans as eng
 left outer join trans as span
              on (span.stringid=eng.id and span.lang='Spanish')
           where eng.lang='English';

The subtle part is the difference between putting "eng.lang='English'" in
the where clause (correct) versus in the on clause (wrong; try it and see
what happens). In the on clause, we create a matching NULL entry due to the
left outer join; in the where clause, we can get rid of the non-English
translations in the eng table, which is what we want.

HTH.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Adam Erickson
> Sent: Monday, May 27, 2002 12:44 AM
> To: Pgsql-Novice
> Subject: [NOVICE] Possible use of a subselect?
>
>
> Greetings All,
>
> I've run into a spot where I think I could use a subselect but
> I'm not sure
> how I would (or if I can).  This is a simple table which holds strings and
> their translated counterparts.
>
> Given the table structure:
> string
> ------
> id serial int4
> stringid int4 not null
> language varchar(32)
> content varchar(255)
>
> stringid would always equal 0 for the English version of any string.
> Otherwise, it points the string.id of the English version.
> Content contains
> the string (or the translated version).  Language simple contains
> "English"
> or "Chinese".
>
> Everything is working great.  I'm trying to get a query that will
> return the
> English version of every string ("SELECT id,content FROM STRING WHERE
> language='English' and stringid=0") and their translated counterpart (say,
> Korean) which would be ("SELECT content FROM string WHERE
> stringid=ID.OF.ENGLISH.VERSION").
>
> Ending up with:
> | English.StringID | English String | Korean String |
> -----------------------------------------------------
> | 1                | Hello          | Whatever      |
> | 2                | Goodbye        | NULL
>          | -----------------------------------------------------
> (NULL meaning that it hasn't been translated)
>
> I could select all English strings, then select the Korean versions in a
> loop but being new to postgres I thought their might be a better way?
>
> Thanks in advance for any help,
> Adam
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Sun, 26 May 2002 23:43:50 -0500, "Adam Erickson" <adamre@cox.net>
wrote:
>Given the table structure:
>string
>------
>id serial int4
>stringid int4 not null
>language varchar(32)
>content varchar(255)
>
>I'm trying to get a query that will return the
>English version of every string ("SELECT id,content FROM STRING WHERE
>language='English' and stringid=0") and their translated counterpart (say,
>Korean) which would be ("SELECT content FROM string WHERE
>stringid=ID.OF.ENGLISH.VERSION").

Adam,
no need for a subselect.  Try an outer join:

SELECT e.id, e.content, k.content
FROM string e LEFT JOIN string k
    ON e.id = k.stringid
WHERE e.stringid = 0 AND k.language = 'Korean';

HTH.
Servus
 Manfred

On Mon, 27 May 2002 09:44:50 +0200, I wrote:

>SELECT e.id, e.content, k.content
>FROM string e LEFT JOIN string k
>    ON e.id = k.stringid
>WHERE e.stringid = 0 AND k.language = 'Korean';
>
>HTH.
No, that doesn't help.  The "k.language = 'Korean'" part belongs into
the ON clause.  Having it in the WHERE clause makes the statement
behave like an INNER JOIN.  So Joel's answer was not only faster, but
definitely better, too.

Servus
 Manfred