Solved

Complex Select Statement

Posted on 2006-11-17
4
606 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]
ID: 17963767
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
ID: 17963768
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
ID: 17963800
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
ID: 17963981
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

910 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

21 Experts available now in Live!

Get 1:1 Help Now