Mr. Puneet Kishor <punk.k...@gmail.com> wrote: > geo table: 39K rows > id max_age min_age > --- ------- -------- > 1 Holocene Holocene > 5 Cambrian Silurian > 12 Cambrian Ordovician > 229 Cretaceous Quaternary > > 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'm not sure I understand. Let's take geo.id = 5, max_age=Cambrian, min_age=Silurian. You say you want a record whose age_bottom is greater than that corresponding to Cambrian, that is 542.0000; and whose age_top is smaller than that corresponding to Silurian, or 416.0000. I don't seem to see any such record in your example. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
