Hello Marco, As far as i can see, the union is necessary. However, the second select in the union can be rewritten as a join:
SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION SELECT prop_key, prop_value FROM MKProperties JOIN MKObjects on MKProperties.obj_id = MKObjects.id WHERE MKObjects.type='PANEL' AND MKObjects.platform='IPHONE' I am not sure if i understood your question correctly - perhaps you want to add some examples. Martin Am 02.02.2011 10:04, schrieb Marco Bambini: > Hello, I have two tables defined as: > > CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, > type TEXT, parent_id INTEGER DEFAULT 0); > CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id > INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)); > > I need to create a query that returns 2 columns key, value (column names are > not important) where the first row is the label 'ID' with value id from > MKObjects and the other rows are the columns prop_key, prop_value from > MKProperties where obj_id= MKObjects.id satisfying a WHERE condition. > > So far I am using a query like: > SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION > SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM > MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id); > > but I am wondering if there is a better way (without using 3 select > statements). > Thanks a lot. > -- > Marco Bambini > http://www.sqlabs.com > > > > > > > _______________________________________________ > 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