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
