Re: sub-query question

Cooking with gas once again ;) Thanks for the info on JOINs!
Scott

On Nov 12, 2004, at 9:52 AM, 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
>