Re: sub-query question

Scott Frankel <leknarf@pacbell.net> writes:
> Here's my query:

> SELECT (
>      u.color_id = (
>          SELECT c.color_name
>             FROM colors c
>                 WHERE color_id = 1)) AS color_name,
>     u.name, u.the_date
>     FROM users u
>          WHERE u.color_id = 1
>          ORDER BY u.the_date DESC LIMIT 1;

I think you want

SELECT
     (SELECT c.color_name
        FROM colors c
        WHERE color_id = u.color_id) AS color_name,
     u.name, u.the_date
  FROM users u
  WHERE u.color_id = 1
  ORDER BY u.the_date DESC LIMIT 1;

The sub-select can refer to a variable of the outer query, as long
as you are careful to qualify it so it can't be mistaken for a variable
of the sub-select itself.

You could also express this query as a join.  If you are pulling a whole
lot of users rows, the join way would probably be more efficient.

            regards, tom lane