Mr. Puneet Kishor <[email protected]> wrote:
>>> 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.
Something like this:
select geo.id, min_age, max_age, age_bottom, age_top, name, color
from geo left join intervals i on i.id = (
select id from intervals
where age_bottom >=
(select age_bottom from intervals where name = geo.max_age)
and age_top <=
(select age_top from intervals where name = geo.min_age)
order by (age_bottom - age_top) limit 1
);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
