inZania <z...@claes.biz> wrote:
> The situation is this: there are two tables, one called "objects"
> (fields: object_id[int] and object_name) and properties (fields:
> object_id[int] and property_name and score[int]). Objects have one
> or more properties.
>
> The objective of the query is to return an object who has all of the
> given properties scored above or below the given value.
>
> For example, I might look for the object_id of an object who has the
> property "green" scored above 3, the property "hot" scored below 2,
> and the property "bright" scored above 0. It might have other
> properties, too, but it must have these 3.
select * from objects where
object_id in (select object_id from properties where
property_name='green' and score > 3) and
object_id in (select object_id from properties where property_name='hot'
and score < 2) and
object_id in (select object_id from properties where
property_name='bright' and score > 0);
> And then, for the second part, I need a query that will select
> properties which are NOT in this set of given properties but are
> relevant to the returned set of objects. In this part, I'm not
> concerned with the property's score - just that it is relevant to the
> objects. In other words, I might now find that the property "hard"
> is relevant to 3/4 objects (even though one of them had a very low
> score for the hard property, another had a very high one, etc).
> Basically I want to know which property, once I can determine the
> score, will help narrow down the object-set the most, so that I can
> collect data for the most relevant property to narrow down the object
> set.
select property_name, count(*) cnt from properties where
object_id in (select object_id from properties p where
p.property_name='green' and p.score > 3) and
object_id in (select object_id from properties p where
p.property_name='hot' and p.score < 2) and
object_id in (select object_id from properties p where
p.property_name='bright' and p.score > 0)
and property_name not in ('green', 'hot', 'bright')
group by property_name
order by cnt desc;
An index on properties(property_name, object_id) will likely help a lot.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
