Thread: SELECT with column specified by subquery

I desire to create a SELECT statement where one of the column names comes
from another table.  Something like:

  SELECT id, (SELECT type FROM favorite_food_type WHERE user = 'ralph')
  FROM foods;

If Ralph's favorite food type is fruit, ultimately I'd like the query to
become:

  SELECT id, fruit FROM foods;

Instead I get:

  SELECT id, 'fruit' FROM foods;

Where 'fruit' is a string and not treated as a column name.  Is there any
way to do this?

Thank you!



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SELECT-with-column-specified-by-subquery-tp5802533.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

webcoyote wrote
> I desire to create a SELECT statement where one of the column names comes
> from another table.  Something like:
>
>   SELECT id, (SELECT type FROM favorite_food_type WHERE user = 'ralph')
>   FROM foods;
>
> If Ralph's favorite food type is fruit, ultimately I'd like the query to
> become:
>
>   SELECT id, fruit FROM foods;
>
> Instead I get:
>
>   SELECT id, 'fruit' FROM foods;
>
> Where 'fruit' is a string and not treated as a column name.  Is there any
> way to do this?
>
> Thank you!

Not using pure SQL.  You can run the favorite food query, save the result to
a variable, then build a dynamic SQL query and inject the value of the
variable in place of where you would place the column name alias. Pick
whatever language/client you wish.

I do not understand how you get:

"SELECT id, 'fruit' FROM foods;"

since the result of a select is a table, not another select...

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SELECT-with-column-specified-by-subquery-tp5802533p5802536.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.