Re: sub-query question - Mailing list pgsql-general
From | Franco Bruno Borghesi |
---|---|
Subject | Re: sub-query question |
Date | |
Msg-id | 4194FF50.1020303@akyasociados.com.ar Whole thread Raw |
In response to | sub-query question (Scott Frankel <leknarf@pacbell.net>) |
List | pgsql-general |
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 >
pgsql-general by date: