Re: Query to return modified results at runtime? - Mailing list pgsql-sql

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