Hello Igor, your query returns 3 columns, but I need just two columns (key, value for example).
The first row should be the label 'ID' and the id of the MKObjects followed by a SELECT prop_key, prop_value WHERE obj_id= MKObjects.id. For example MKObjects contains (1,IPHONE,PANEL,0) and MKProperties contains (1,1,NAME,About Box) and (2,1,WIDTH,200) the result of the query should should be: col1 col2 'ID' 1 'NAME' 'About Box' 'WIDTH', '200' -- Marco Bambini http://www.sqlabs.com On Feb 2, 2011, at 1:43 PM, Igor Tandetnik wrote: > Marco Bambini <ma...@sqlabs.net> wrote: >> 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. > > You don't really need, or want, to create a query like that. It goes against > the grain of SQL. You want this: > > select o.id, prop_key, prop_value > from MKObjects o join MKProperties p on (o.id = p.obj_id) > where type='PANEL' AND platform='IPHONE' > order by o.id; > > When formatting your report, output a section heading whenever id column > changes from previous row. > -- > Igor Tandetnik > > _______________________________________________ > 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
