Subject: Re: Query to return modified results at runtime?
From: Kenneth B Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: George Handin <postgresql@dafunks.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <44876F63.9030302@dafunks.com>
References: <44876F63.9030302@dafunks.com>
Content-Type: text/plain
Organization: Scotts Hill Associates, Inc.
Date: Thu, 08 Jun 2006 08:51:48 -0700
Message-Id: <1149781908.1645.14.camel@localhost>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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