Re: sql join question - Mailing list pgsql-general

From Scott Frankel
Subject Re: sql join question
Date
Msg-id 429dd4c7ee91a1c73aea17c0316ecd0f@pacbell.net
Whole thread Raw
In response to Re: sql join question  (Ragnar Hafstað <gnari@simnet.is>)
Responses Re: sql join question  (Ragnar Hafstað <gnari@simnet.is>)
List pgsql-general
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
>


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: basic temp table question
Next
From: Jonathan Hedstrom
Date:
Subject: Re: Trigger email?