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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
