Re: [sqlite] help with a complicated join of two tables

2011-09-14 Thread Petite Abeille
On Sep 14, 2011, at 8:40 PM, Igor Tandetnik wrote: > Think about it this way. You have a phone book, where names are sorted by > last name, then first name. You want to find all people whose last name is > greater than 'Smith' and first name less than 'John'. The alphabetic order > helps you

Re: [sqlite] help with a complicated join of two tables

2011-09-14 Thread Igor Tandetnik
On 9/14/2011 2:07 PM, Jan Hudec wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: 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

Re: [sqlite] help with a complicated join of two tables

2011-09-14 Thread Jan Hudec
On Mon, Sep 12, 2011 at 12:16:55 -0400, Igor Tandetnik wrote: > On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote: > > > >On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: > >>Something like this: > >> > >>select geo.id, min_age, max_age, age_bottom, age_top, name, color > >>from geo left join

Re: [sqlite] help with a complicated join of two tables

2011-09-12 Thread Igor Tandetnik
On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: 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>=

Re: [sqlite] help with a complicated join of two tables

2011-09-12 Thread Mr . Puneet Kishor
On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: > Mr. Puneet Kishor 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

Re: [sqlite] help with a complicated join of two tables

2011-09-12 Thread Igor Tandetnik
Mr. Puneet Kishor 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 >>>

Re: [sqlite] help with a complicated join of two tables

2011-09-11 Thread Mr. Puneet Kishor
On Sep 11, 2011, at 9:58 PM, Igor Tandetnik wrote: > Mr. Puneet Kishor wrote: >> geo table: 39K rows >> id max_age min_age >> --- --- >> 1 Holocene Holocene >> 5 Cambrian Silurian >> 12 Cambrian Ordovician >> 229 Cretaceous Quaternary >> >> intervals table:

Re: [sqlite] help with a complicated join of two tables

2011-09-11 Thread Igor Tandetnik
Mr. Puneet Kishor 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 > --- --

[sqlite] help with a complicated join of two tables

2011-09-11 Thread Mr. Puneet Kishor
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