From: Kenneth B Hill <ken@scottshill.com>
To: George Handin <postgresql@dafunks.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query to return modified results at runtime?
Date: Thu, 08 Jun 2006 08:51:48 -0700
Message-ID: <1149781908.1645.14.camel@localhost> (raw)
In-Reply-To: <44876F63.9030302@dafunks.com>
References: <44876F63.9030302@dafunks.com>
On Wed, 2006-06-07 at 19:29 -0500, George Handin wrote:
> I have a query:
>
> SELECT * FROM testtable;
>
> Where the results are:
>
> ID Color
> --- -------
> 1 Blue
> 2 Red
> 3 Green
> 4 Orange
>
> How would I rewrite the query to return results where the colors are
> replaced by letters to give the following results?
>
> ID Color
> --- -------
> 1 A
> 2 D
> 3 B
> 4 C
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
It looks like you may want to use a validation table:
Name: color_validate
ID Color Code
--- --- ---
1 Blue A
2 Red D
3 Green B
4 Orange C
Then try the following query:
SELECT table_a.ID, color_validate.Code
FROM table_a, color_validate
WHERE (table_a.ID = color_validate.ID);
-Ken
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ken@scottshill.com, postgresql@dafunks.com
Subject: Re: Query to return modified results at runtime?
In-Reply-To: <1149781908.1645.14.camel@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox