Hi i need some help with this problem.
I am working web application and for database i am using sqlite. Can someone
help me with one query from databse which must be optimized == fast =)
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
....
ID_DISH is id of different dishes, ID_INGREDIENT is ingredient which dish is
made of:
so in my case dish with id 1 is made with ingredients with ids 2,3
In this table a have more then 15000 rows and my question is:
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,....
my query is:
SELECT t2.ID_dish, (SELECT COUNT(*) as c FROM dishIngredient as t1 WHERE
t1.ID_ingredient NOT IN (2,4) AND t1.ID_dish = t2.ID_dish GROUP BY ID_dish)
as c FROM dishIngredient as t2 WHERE t2.ID_ingredient IN (2,4) GROUP BY
t2.ID_dish ORDER BY c ASC
but it is so lame =)
thanks for your replay
--
View this message in context:
http://old.nabble.com/help-with-optimazing-sql-query-tp30608918p30608918.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
