Help with a query

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with a query

Marco Bambini
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?
--
Marco Bambini
http://www.sqlabs.com






_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with a query

Simon Davies
On 4 March 2011 10:10, Marco Bambini <[hidden email]> 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?

select
        t1.obj_id
from
        MKProperties as t1
join
        MKProperties as t2
on
        t1.obj_id=t2.obj_id
where
        t1.prop_key='PARENTID' and
        t1.prop_value='0' and
        t2.prop_key='RESOURCE_ORDER'
order by t2.prop_value;

> --
> Marco Bambini
> http://www.sqlabs.com
>

Regards,
Simon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with a query

Robert Hairgrove
In reply to this post by Marco Bambini
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with a query

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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users