Обсуждение: sub-query question
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');
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 >
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
On Fri, Nov 12, 2004 at 09:52:09AM -0800, 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? You're talking about joins. http://www.postgresql.org/docs/7.4/static/tutorial-join.html http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html > 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 > > test=# SELECT * from colors; > color_id | color_name > ----------+------------ > 1 | red > 2 | green > 3 | blue There are at least four ways to write the join query you want: SELECT color_name, name, the_date FROM users NATURAL JOIN colors; SELECT color_name, name, the_date FROM users JOIN colors USING (color_id); SELECT color_name, name, the_date FROM users JOIN colors ON colors.color_id = users.color_id; SELECT color_name, name, the_date FROM users, colors WHERE users.color_id = colors.color_id; Debate exists about which of the above is "better." I tend to use the second and third forms because I think they most clearly document how the tables are joined, and because I think of the WHERE clause as meaning "...and here are the records I want from all that." Of course that's just my opinion. I could be wrong.
On Fri, Nov 12, 2004 at 11:26:14AM -0700, Michael Fuhr wrote: > There are at least four ways to write the join query you want: I may have misunderstood what results you're looking for, but the examples I gave may nevertheless be useful. Sorry if they cause any confusion. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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 >