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. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
