Thread: sql join question
I want to return all records that match criteria across three separate
tables
and , in spite of reading up on joins, have so far been unable to
design a
solution that doesn't require caching a hash table of intermediate
results.
Here's the situation:
Let's say color names belong to a set of tones, each of which belong to
a
palette. A palette can be comprised of multiple tones. Each tone can
contain multiple color names. i.e.:
palette palette1
tones red, green
colors rose madder, crimson, red ochre, phthalocyanine, leaf green
palette palette2
tones blue
colors cerulean
palette palette3
tones yellow
colors chrome
Task: find all color names in each of palette1's tones.
Can this be done in a single SQL statement? Or does it require storing
the results of a select to find each of the tones that belong to
palette1, then
separate selects on each resultant tone to yield the 5 color names?
Thanks in advance!
Scott
p.s. Here's my test case sql:
CREATE TABLE palettes (palette_pkey SERIAL PRIMARY KEY,
palette_name text UNIQUE DEFAULT NULL);
INSERT INTO palettes (palette_name) VALUES ('plt1');
INSERT INTO palettes (palette_name) VALUES ('plt2');
INSERT INTO palettes (palette_name) VALUES ('plt3');
CREATE TABLE tones (tone_pkey SERIAL PRIMARY KEY,
tone_name text UNIQUE DEFAULT NULL, palette_pkey integer REFERENCES
palettes);
INSERT INTO tones (tone_name, palette_pkey) VALUES ('red', 1);
INSERT INTO tones (tone_name, palette_pkey) VALUES ('green', 1);
INSERT INTO tones (tone_name, palette_pkey) VALUES ('blue', 2);
INSERT INTO tones (tone_name, palette_pkey) VALUES ('yellow', 3);
CREATE TABLE colors (color_pkey SERIAL PRIMARY KEY,
color_name text UNIQUE DEFAULT NULL, tone_pkey integer REFERENCES
tones);
INSERT INTO colors (color_name, tone_pkey) VALUES ('rose madder', 1);
INSERT INTO colors (color_name, tone_pkey) VALUES ('crimson', 1);
INSERT INTO colors (color_name, tone_pkey) VALUES ('red ochre', 1);
INSERT INTO colors (color_name, tone_pkey) VALUES ('phthalocyanine',
2);
INSERT INTO colors (color_name, tone_pkey) VALUES ('leaf green', 2);
INSERT INTO colors (color_name, tone_pkey) VALUES ('cerulean', 3);
INSERT INTO colors (color_name, tone_pkey) VALUES ('chrome', 4);
# -1- [ cache results in a hash table for further processing ]
SELECT * FROM tones WHERE palette_pkey = 1;
# yields
# tone_pkey | tone_name | palette_pkey
# -----------+-----------+--------------
# 1 | red | 1
# 2 | green | 1
# -2- [ for each tone returned from step 1 ]
SELECT * FROM colors WHERE tone_pkey = 1;
# yields
# color_pkey | color_name | tone_pkey
# ------------+-------------+-----------
# 1 | rose madder | 1
# 2 | crimson | 1
# 3 | red ochre | 1
SELECT * FROM colors WHERE tone_pkey = 2;
# yields
# color_pkey | color_name | tone_pkey
# ------------+----------------+-----------
# 4 | phthalocyanine | 2
# 5 | leaf | 2
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
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
>
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
Got it. Thanks! Scott On Mar 1, 2005, at 10:52 PM, Ragnar Hafstað wrote: > 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 > > >