sql join question

[Scott Frankel <leknarf@xxxxxxxxxxx>]

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






---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org