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
