Re: Query to return modified results at runtime?

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