Skip site navigation (1) Skip section navigation (2)

Re: Possible use of a subselect?

From: "Joel Burton" <joel(at)joelburton(dot)com>
To: "Adam Erickson" <adamre(at)cox(dot)net>,"Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Possible use of a subselect?
Date: 2002-05-27 06:21:54
Message-ID: (view raw or whole thread)
Lists: 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,
            from trans as eng
 left outer join trans as span
              on ( 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.


- J.

Joel BURTON | joel(at)joelburton(dot)com | | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)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 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
> 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

In response to

pgsql-novice by date

Next:From: John TaylorDate: 2002-05-27 07:12:43
Subject: Re: Copy Comand question
Previous:From: Adam EricksonDate: 2002-05-27 04:43:50
Subject: Possible use of a subselect?

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group