From: | Scott Frankel <leknarf(at)pacbell(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | sql join question |
Date: | 2005-03-01 21:42:18 |
Message-ID: | 77bb2c83c315c27753cee62ca04b50b5@pacbell.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Edmund Bacon | 2005-03-01 21:43:11 | Re: Novice Question |
Previous Message | Sean Davis | 2005-03-01 21:34:42 | Re: Novice Question |