Re: [sqlite] help with a query using between with a select staement
On Jun 19, 2011, at 12:06 PM, looki wrote: > and a table 'foods' which also holds a 'time' column and i now want to know > all entries in foods which time is between one of the times in t. for example: select foods.id fromfoods where exists (select 1 from symptoms where foods.time between symptoms.start_time and symptoms.end_time ) > but this gives me an error. Indeed, making up your own syntax is not going to fly. When in doubt, check the friendly instruction manual: http://www.sqlite.org/lang.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] help with a query using between with a select staement
Hi, i have a select stamement which holds two dates, something like: 2011-12-31 09:002011-12-30 21:00 2011-12-31 18:002011-12-31 06:00 ... let me call this t. and a table 'foods' which also holds a 'time' column and i now want to know all entries in foods which time is between one of the times in t. my approach was: select time, datetime(time, '-4 hours') from symptoms as t; select id from food where time between any t; but this gives me an error. Thanks for your help! Tobias -- View this message in context: http://old.nabble.com/help-with-a-query-using-between-with-a-select-staement-tp31878758p31878758.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a query
Thanks a lot Simon and Robert. -- Marco Bambini http://www.sqlabs.com On Mar 4, 2011, at 11:50 AM, Robert Hairgrove wrote: > On Fri, 2011-03-04 at 11:10 +0100, Marco Bambini wrote: >> Hello, >> I have a table defined as: >> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id >> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) >> >> In that table there some rows like: >> obj_id prop_keyprop_value >> 1PARENTID0 >> 1RESOURCE_ORDER 0 >> 2PARENTID0 >> 2RESOURCE_ORDER 1 >> 3PARENTID0 >> 3RESOURCE_ORDER 3 >> >> I need a query that returns all the obj_id with prop_key='PARENTID' AND >> prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. >> Any help? > > Sounds like a job for a self-join. Try this: > > SELECT T1.obj_id, T2.prop_value > FROM MKProperties T1 > INNER JOIN MKProperties T2 > ON (T1.obj_id = T2.obj_id) > WHERE T2.prop_key = 'RESOURCE_ORDER' > AND T1.prop_key = 'PARENT_ID' > AND T1.prop_value = 0 > ORDER BY T2.prop_value; > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a query
On Fri, 2011-03-04 at 11:10 +0100, Marco Bambini wrote: > Hello, > I have a table defined as: > CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id > INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) > > In that table there some rows like: > obj_idprop_keyprop_value > 1 PARENTID0 > 1 RESOURCE_ORDER 0 > 2 PARENTID0 > 2 RESOURCE_ORDER 1 > 3 PARENTID0 > 3 RESOURCE_ORDER 3 > > I need a query that returns all the obj_id with prop_key='PARENTID' AND > prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. > Any help? Sounds like a job for a self-join. Try this: SELECT T1.obj_id, T2.prop_value FROM MKProperties T1 INNER JOIN MKProperties T2 ON (T1.obj_id = T2.obj_id) WHERE T2.prop_key = 'RESOURCE_ORDER' AND T1.prop_key = 'PARENT_ID' AND T1.prop_value = 0 ORDER BY T2.prop_value; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a query
On 4 March 2011 10:10, Marco Bambini wrote: > Hello, > I have a table defined as: > CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id > INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) > > In that table there some rows like: > obj_id prop_key prop_value > 1 PARENTID 0 > 1 RESOURCE_ORDER 0 > 2 PARENTID 0 > 2 RESOURCE_ORDER 1 > 3 PARENTID 0 > 3 RESOURCE_ORDER 3 > > I need a query that returns all the obj_id with prop_key='PARENTID' AND > prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. > Any help? select t1.obj_id from MKProperties as t1 join MKProperties as t2 on t1.obj_id=t2.obj_id where t1.prop_key='PARENTID' and t1.prop_value='0' and t2.prop_key='RESOURCE_ORDER' order by t2.prop_value; > -- > Marco Bambini > http://www.sqlabs.com > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with a query
Hello, I have a table defined as: CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) In that table there some rows like: obj_id prop_keyprop_value 1 PARENTID0 1 RESOURCE_ORDER 0 2 PARENTID0 2 RESOURCE_ORDER 1 3 PARENTID0 3 RESOURCE_ORDER 3 I need a query that returns all the obj_id with prop_key='PARENTID' AND prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. Any help? -- Marco Bambini http://www.sqlabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
