Using SQL Server 2000 is there an easier way to do this? Part of this is going to my own psuedocode, but the bulk is TRANSACT-SQL Before someone suggests cursors, I figured a cursor would be really slow.. What i am really looking for is a single select query that will get me the same results. FOREACH(@idorder in OrdersTable) { DECLARE @filledcount smallint DECLARE @orderrows smallint SELECT @filledcount = count(idorderdetails) FROM orders_details , products WHERE products.idproduct = orders_details.idproduct AND ISNULL(quantity,0) <= ISNULL(received,0) + ISNULL(stock,0) and idorder = @idorder select @orderrows = count(idorderdetails) from orders_details where idorder = @idorder }

As I doubt any of us will know the layout and relationships between your tables, maybe you could tell us what "results" you want, rather then us trying to figure out what part of this sequence of operations is actually important to you, and what is just a byproduct of the operations themselves (you've basically dropped a pile of code on us and asked us to "make it better", without any parameters as to what it should really be doing, and therefore no idea what this "better" state would be like).

oh heh, when i made the post it was clear to me, but now i see why someone would have problems understanding it.


What it is, is I have a table called orders, and a table called orders_details. Orders_details are simply the products that someone ordered on a particular order.
In orders_details there are columns 'quantity' and 'received'. quantity is the amount needed for the order, 'received' is the amount currently allocated to the order. products.stock is the amount of the product that is currently in stock for the product that was ordered.

The first select statement:

SELECT @filledcount = count(idorderdetails)
FROM orders_details , products
WHERE products.idproduct = orders_details.idproduct
AND ISNULL(quantity,0) <= ISNULL(received,0) + ISNULL(stock,0)
and idorder = @idorder


determines which orders_details can be filled, and counts how many of those lines can be filled.

The second statement:

select @orderrows = count(idorderdetails)
from orders_details
where idorder = @idorder

Counts the number of lines on the order.

If the numbers from the 2 queries are the same, that means that the order is able to be shipped.

So, what i want to do is in theory this:

Select all orders that have been filled and are able to be shipped.

I've received the solution to my problem.


using exists statements just do a select


select idorder from orders
where exists (select idorder from orders_details where orders_details is filled)
and not exists (select idorder from orders_details where orders_details is not filled)


pretty simple... why didnt i think of it?