Apologies in advance for a terrible subject line -- I didn't know quite how to
phrase it better.
I have the following two tables (with sample data)
CREATE TABLE geo (
id INTEGER PRIMARY KEY,
max_age TEXT,
min_age TEXT
);
geo table: 39K rows
id max_age min_age
--- ------- --------
1 Holocene Holocene
5 Cambrian Silurian
12 Cambrian Ordovician
229 Cretaceous Quaternary
CREATE TABLE intervals (
id INTEGER PRIMARY KEY,
age_bottom REAL,
age_top REAL,
name TEXT,
color TEXT
);
intervals table: ~450 rows
id age_bottom age_top name color
--- ---------- ------- ----------- -------
3 0.0117 0.0000 Holocene #FEF2E0
105 443.7000 416.0000 Silurian #B3E1B6
112 488.3000 443.7000 Ordovician #009270
421 2.5880 0.0000 Quaternary #F9F97F
122 542.0000 488.3000 Cambrian #7FA056
33 145.5000 65.5000 Cretaceous #7FC64E
Keep in mind, max_age is older than min_age, and age_bottom is older than
age_top.
The table geo can also have rows with min_age = max_age. I want a result set
with geo.id, min_age, max_age, age_bottom, age_top, name, color like so:
- every row should be for one and only one geo record. I have 39K rows in "geo"
table, so the result set should have 39K rows.
- when min_age = max_age, list the corresponding intervals.name and color
- when min_age != max_age, find the interval with the smallest different
between age_bottom and age_top that would span min_age and max_age of geo. In
other words, the interval whose age_bottom is bigger than the age_bottom of the
max_age and whose age_top is smaller than the age_top of the min_age.
I've gotten so far
SELECT g.id, max_age, min_age,
i.age_bottom, i.age_top, i.interval_name, i.interval_color
FROM geo g
LEFT JOIN intervals i_max ON g.max_age = i_max.interval_name
LEFT JOIN intervals i_min ON g.min_age = i_min.interval_name
JOIN intervals i ON
i.age_bottom >= i_max.age_bottom AND
i.age_top <= i_min.age_top
WHERE g.id = ?
ORDER BY i.age_bottom - i.age_top
LIMIT 1;
The above query finds the correct values for a single g.id, but I want a result
set with all the rows.
Suggestions?
Puneet.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
