Complex Select Statement
Posted on 2006-11-17
I have an application that runs with a Database on MS SQL-Server 2000 and contains data of approx 20.000 kits containig of 100.000 parts.
There are two tables, one containing the kits and one table containing the parts.
I want to select all the kits which consist of parts that are in stock.
Kits which have a part which is not on stock shall not be selected.
Up to now, I perform this in two steps:
1. select all KitIDs from kits
2. Perform a loop for each Kit in which I do the following:
KitDeliverable = True
select parts.OnStock from parts where parts.KitID=KitID
Do while ReadDataset
If OnStock = 0 then
KitDeliverable = False
Unfortunately we are running into heavy performanceproblems with that method.
Is there a way of solving this task with only one single select statement that has a better performance?
Thank you very much in advance!