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