Igor Tandetnik wrote:
>
> On 1/6/2011 3:50 PM, gasperhafner wrote:
>> I have table x:
>>
>> ID | ID_DISH | ID_INGREDIENT
>> 1 | 1 | 2
>> 2 | 1 | 3
>> 3 | 1 | 8
>> 4 | 1 | 12
>>
>> 5 | 2 | 13
>> 6 | 2 | 5
>> 7 | 2 | 3
>>
>> 8 | 3 | 5
>> 9 | 3 | 8
>> 10| 3 | 2
>>
>> i need query which will fetch rows where i can find ids of dishes ordered
>> by
>> count of ingreedients ASC which i haven added to my algoritem.
>>
>> examle: foo(2,4)
>> will rows in this order:
>>
>> ID_DISH | count(stillMissing)
>> 10 | 2
>> 1 | 3
>>
>> Dish with id 10 has ingredients with id 2 and 4 and hasn't got 2 more,
>> then
>> is dish with id 1 which has ingredient with id 2 and hassn't got 3
>> more,....
>
> select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing
> from x
> group by ID_DISH
> having stillMissing != count(*)
> order by stillMissing;
>
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
Thanks!
It is working fast!!!
What about if a want sort by ingredients asc which i have?
--
View this message in context:
http://old.nabble.com/help-with-optimazing-sql-query-tp30608918p30609668.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
