On 1/6/2011 6:44 PM, gasperhafner wrote: > I would be very grateful if you can explain your > last query line by line.
select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing from x group by ID_DISH having stillMissing != count(*) order by stillMissing * 1.0 / count(*); Perhaps the most non-obvious part here is sum(ID_INGREDIENT not in (2, 4)), if you haven't seen this trick before. "ID_INGREDIENT not in (2, 4)" is a boolean expression that, for a given ingredient, returns "true" if it's not in the list and "false" if it is. "True" and "false" are in scare quotes for a reason: SQLite lacks a dedicated boolean type, and uses integers 1 and 0 instead (the same trick works in other DBMS, just with a less concise syntax). SUM() aggregate function, in conjunction with GROUP BY clause, adds up these zeros and ones over all ingredients of each dish, which effectively produces a count of all missing ingredients. The rest is pretty simple. count(*) is the number of all ingredients in the dish. HAVING clause then filters out those dishes where all the ingredients are missing - in other words, those that have no ingredients from the list at all. ORDER BY clause implements your "order by percentage of missing stuff" condition; "* 1.0" part is there just to force SQLite to perform floating point division (the default, when dividing two integers, is a C-style truncating division). -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
