Solved

Complex Select Statement

Posted on 2006-11-17
4
601 Views
Last Modified: 2008-01-09
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
Comment
Question by:Arthur_2000
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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
 
LVL 6

Accepted Solution

by:
wtapsell earned 250 total points
Comment Utility
You could try this:

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

0
 

Author Comment

by:Arthur_2000
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now