Re: Possible use of a subselect? - Mailing list pgsql-novice

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
>