Re: sql join question

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.:

-1- these tables yield rows from a NATURAL JOIN query
    CREATE TABLE palettes (palette_pkey SERIAL PRIMARY KEY,
        palette_name text UNIQUE DEFAULT NULL);

    CREATE TABLE tones    (tone_pkey SERIAL PRIMARY KEY,
        tone_name text UNIQUE DEFAULT NULL,
        palette_pkey integer REFERENCES palettes);

-2- these tables yield NO rows from a NATURAL JOIN query
    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 offending columns are inconsequential for
what I'm
trying to do with this operation.  Can they be suppressed from the
query for this
SQL statement to function properly?  Or am I SOL?

Thanks again!
Scott





On Mar 1, 2005, at 2:28 PM, Ragnar Hafstað wrote:

> On Tue, 2005-03-01 at 13:42 -0800, Scott Frankel wrote:
>
>> [snip problem]
>
>> Task:  find all color names in each of palette1's tones.
>>
>> Can this be done in a single SQL statement?
>
>> [snip table examples]
>
> looks like a job for NATURAL JOIN
>
> test=# select color_name
>        from palettes
>             natural join tones
>             natural join colors
>        where palette_name='plt1';
>
>    color_name
> ----------------
>  rose madder
>  crimson
>  red ochre
>  phthalocyanine
>  leaf green
> (5 rows)
>
>
> gnari
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>