Re: sub-query question

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.