• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

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
        Loop

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!
0
Arthur_2000
Asked:
Arthur_2000
1 Solution
 
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

0
 
TimCotteeHead of Software ServicesCommented:
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
0
 
wtapsellCommented:
You could try this:

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

0
 
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!
0
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now