January 6, 2005 at 7:55 pm
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 do not want to use views because they are unneeded and slow, and I havn't been able to get the right results without using a view. Can someone assist me in creation of a query that is not a view?
January 10, 2005 at 10:10 am
Josh,
I'm not sure why you think views are slow. They should not add appreciable overhead to your query. In fact, anything done in a view can be done with a single statement, but the SQL gets very complex to maintina with the subselects embedded within.
While I might be able to do it, can you post a few sample rows from the tables to show what you have? Makes it easier to see what your schema looks like. Only post the relevant columns.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply