Re: sql join question

On Tue, 2005-03-01 at 16:51 -0800, Scott Frankel wrote:
> Sweet!  And not so sweet.
>
> The natural join worked beautifully with my test schema; but it failed
> to yield any rows with my real-world schema.  I think I've tracked down
> why:  duplicate column names.  i.e.:
> ...
>     CREATE TABLE palettes (palette_pkey SERIAL PRIMARY KEY,
>         palette_name text UNIQUE DEFAULT NULL,
>         qwe text);
>
>     CREATE TABLE tones    (tone_pkey SERIAL PRIMARY KEY,
>         tone_name text UNIQUE DEFAULT NULL,
>         palette_pkey integer REFERENCES palettes,
>         qwe text);
>
> Are the 'qwe' columns in both tables clobbering each other and
> preventing the
> join from succeeding?

the docs really explain this better than I can, but a
  table1 NATURAL JOIN table2
is shorthand fo a
  table1 JOIN table2 USING (list_of_common_keys)

so:
select color_name from palettes
   join tones USING (palette_pkey)
   join colors USING (tone_pkey)
  where palette_name='plt1';

see:
http://www.postgresql.org/docs/8.0/interactive/sql-select.html

gnari