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_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? > > 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
