Re: sub-query question

Scott Frankel <leknarf(at)pacbell(dot)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