Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • 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
 
TimCotteeCommented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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