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.