Jump to content
  • Advertisement
Sign in to follow this  
Rain Dog

SQL question.

This topic is 5030 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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 }

Share this post


Link to post
Share on other sites
Advertisement
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).

Share this post


Link to post
Share on other sites
Looking at the Query (With out table schema) if you have orders and order deatils you have a joining field... Why not use that and group by the joined data.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!