Mr. Puneet Kishor <punk.k...@gmail.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users