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