SQL query assistance. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL query assistance.

Author

Topic

rain_dog
Starting Member

3 Posts

Posted - 2005-01-06 : 21:51:44

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?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-06 : 22:39:01

quote:
I do not want to use views because they are unneeded and slow

Where did you get this opinion from?

quote:
and I havn't been able to get the right results without using a view

Well, if both statements were true you'd be pretty much screwed then. Luckily they're not.

Can you post the rest of your code? It will make it easier to provide a solution.

rain_dog
Starting Member

3 Posts

Posted - 2005-01-06 : 22:56:57

I got the opinion from SQL Server's Query Analyzer.

All i need is a query that will work like this:

Select * from orders
where orders.idorder IS IN FILLEDORDERSQUERY

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-07 : 00:09:08

do you have a field for ordernumber which will serve as your pk in orders and foreign key in orders_details?

probably you're looking for a simple join?

or i've misinterpreted your requirements. post some ddl,dml, sample
of what you need to retrieve

--------------------
keeping it simple...

rain_dog
Starting Member

3 Posts

Posted - 2005-01-07 : 07:20:57

yeah, idorder is the pk, idorder is also the foreign key for the orders_details table.

so you could do this:

select * from orders, orders_details
where orders.idorder = orders_details.idorder
and orders.orderstatus = 1

To get all the orders_details rows where the orderstatus is 1.

But how can I expand the join to include the above statement?

I for the life of me can't get the join right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources