Re: [sqlite] help with optimazing sql query

2011-01-06 Thread Igor Tandetnik
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

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread gasperhafner
Igor Tandetnik wrote: > > On 1/6/2011 6:11 PM, gasperhafner wrote: >> ID_dish | ID_ingredient >> 1 | 355 >> 1 | 390 >> 1 | 217 >> 1 | 23 >> 1 | 261 >> 2 | 92 >> 2 | 377 >> 2 | 23 >> 2 | 365 >> >> i have ingredients with ID_

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread Igor Tandetnik
On 1/6/2011 6:11 PM, gasperhafner wrote: > ID_dish | ID_ingredient > 1 | 355 > 1 | 390 > 1 | 217 > 1 | 23 > 1 | 261 > 2 | 92 > 2 | 377 > 2 | 23 > 2 | 365 > > i have ingredients with ID_ingredient (355, 390, 217, 23, 261) > with

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread gasperhafner
Igor Tandetnik wrote: > > On 1/6/2011 5:54 PM, gasperhafner wrote: >> Igor Tandetnik wrote: >>> >>> On 1/6/2011 5:25 PM, gasperhafner wrote: Igor Tandetnik wrote: > > select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing > from x > group by ID_DISH > having stil

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread Igor Tandetnik
On 1/6/2011 5:54 PM, gasperhafner wrote: > Igor Tandetnik wrote: >> >> On 1/6/2011 5:25 PM, gasperhafner wrote: >>> Igor Tandetnik wrote: select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing from x group by ID_DISH having stillMissing != count(*) order by stil

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread gasperhafner
Igor Tandetnik wrote: > > On 1/6/2011 5:25 PM, gasperhafner wrote: >> Igor Tandetnik wrote: >>> >>> select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing >>> from x >>> group by ID_DISH >>> having stillMissing != count(*) >>> order by stillMissing; >> >> What about if a want sort by ing

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread Igor Tandetnik
On 1/6/2011 5:25 PM, gasperhafner wrote: > Igor Tandetnik wrote: >> >> select ID_DISH, sum(ID_INGREDIENT not in (2, 4)) stillMissing >> from x >> group by ID_DISH >> having stillMissing != count(*) >> order by stillMissing; > > What about if a want sort by ingredients asc which i have? order by co

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread gasperhafner
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

Re: [sqlite] help with optimazing sql query

2011-01-06 Thread Igor Tandetnik
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 >

[sqlite] help with optimazing sql query

2011-01-06 Thread gasperhafner
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