Re: [GENERAL] sub-query question

[Franco Bruno Borghesi]

something == otherthing is a boolean expression, you are asking the 
database to compare both values, u.color_id is not equal c.color_name, 
that's why you get 'f'.

I guess that you want to replace the color_id from users by the 
corresponding color_name from colors:

SELECT
   c.color_name, u.name, u.the_date
FROM
   users u
   INNER JOIN colors c ON (u.color_id=c.color_id)
WHERE
   u.color_id = 1
ORDER BY u.the_date DESC LIMIT 1;
If you were trying to do another thing, I'm sorry, I didn't get it :(
Scott Frankel wrote:
How does one embed a sub-query lookup to one table in order to
replace a foreign key id number with it's name in a SELECT on a
second table?
i.e.:  given the following two tables, I want to replace the color_id 
of 1
with the color_name 'red.'  (The SQL to create the two tables follows
below.)

test=# SELECT * from users ;
 color_id | name |  the_date
----------+------+------------
        1 | john | 2004-03-10
        3 | jane | 2004-04-12
        1 | joe  | 2004-05-14
        2 | jepe | 2004-06-16
(4 rows)
test=# SELECT * from colors;
 color_id | color_name
----------+------------
        1 | red
        2 | green
        3 | blue
(3 rows)
My attempts yield an 'f' which looks suspiciously like a boolean false.
Is there an ordering issue with my sub-query, such that the sub-query
doesn't have enough info to perform its lookup?
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;
It returns:
 color_name | name |  the_date
------------+------+------------
 f          | joe  | 2004-05-14
(1 row)
Thanks!
Scott
Here's the SQL to create my test tables:
CREATE TABLE colors (color_id SERIAL PRIMARY KEY, color_name text);
CREATE TABLE users  (color_id integer REFERENCES colors, name text, 
the_date date);

INSERT INTO colors  (color_name) VALUES ('red');
INSERT INTO colors  (color_name) VALUES ('green');
INSERT INTO colors  (color_name) VALUES ('blue');
INSERT INTO users   (color_id, name, the_date) VALUES (1, 'john', 
'2004-03-10');
INSERT INTO users   (color_id, name, the_date) VALUES (3, 'jane', 
'2004-04-12');
INSERT INTO users   (color_id, name, the_date) VALUES (1, 'joe',  
'2004-05-14');
INSERT INTO users   (color_id, name, the_date) VALUES (2, 'jepe', 
'2004-06-16');



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match