Complex Select Statement

Hello Experts,
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
               Exit Do
           End if

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!
Who is Participating?
wtapsellConnect With a Mentor Commented:
You could try this:

select      *
from      Kits
where      kitID not in(
            select distinct KitID
            from      parts
            where      OnStock = 0

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about running this query:

select k.KitIDs, p.OnStock
from kits k
left join parts p
  on p.kitid = k.kitid
 and p.OnStock = 0

Hi Arthur_2000,

Select KitID From Kits Inner Join (Select KitID,Min(OnStock) As Stock From Parts Group By KitID) Parts On Kits.KitID = Parts.KitID Where Parts.Stock > 0

This will get the min stock value for each kit, if this is zero or less (shouldn't be less of course) then it is excluded from the final results.

Tim Cottee
Arthur_2000Author Commented:
Thank you to all of you, who answered my question!
Probably all answers were right, but I took the third answer because I understood best how it worked.
You helped me a great lot!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.