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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
