Simplifying 3 table IN/NOT IN View

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)

Simplifying 3 table IN/NOT IN View

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-17 : 07:53:20

Jeff writes "I wrote a SQL View last night to generate a report of orders that are ready to be filled for an online store.

The 3 tables used in the db are Products, Orders, and OrderItems. OrderItems contains a record for each product in an order. Products has a boolean field "instock" that stored whether a product is in stock. Only orders that have all of their items in stock should show up as the results.

The view I wrote works fine, and I'm not really worried about perfomance as it will only be called maybe a few times in a day. My gut just tells me this is more complex that it needs to be (since it's nested 3 deep) and I'd like to simplify it if possible. Any suggestions would be appreciated. (StatusID = 2 or 3 just means that order has been paid for)

SELECT *
FROM dbo.t_Orders
WHERE (StatusID = 2 OR StatusID = 3) AND
(OrderID IN
(SELECT DISTINCT OrderID FROM dbo.t_OrderItems WHERE
(OrderID NOT IN
(SELECT DISTINCT A.OrderID FROM dbo.t_OrderItems A INNER JOIN dbo.t_Products B ON A.ProductID = B.ProductID WHERE (B.instock = 0)))))"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 08:39:36

Something like this:

Select o.*
from t_Orders o
join
(select distinct OrderID 
 from t_OrderItems A INNER JOIN dbo.t_Products B 
 ON A.ProductID = B.ProductID WHERE B.instock <> 0) as t
on o.orderid = t.orderid
where o.StatusID in (2,3)

Harsh Athalye
India.
"Nothing is Impossible"

Jeff S
Starting Member

2 Posts

Posted - 2006-10-18 : 04:31:01

The problem with that approach is that the subquery returns all the orders that have an instock item, which is different than all of the orders where all of the items in the order are instock (or in other words no items in the order are out of stock).

As an example my query on test data returns 11 results, where the suggested one returns 19.

Jeff S
Starting Member

2 Posts

Posted - 2006-10-18 : 04:44:41

I think the EXCEPT function in SQL 2005 would accomplish what I need, but I'm running 2000 so it's not an option. If there's a way to do a set difference in sql 2000 that's easier than IN - NOT IN then that's probably what i'm looking for.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-18 : 05:40:19

how about somthing like this


Select * From 
(
 	Select * From dbo.t_orders t  where StatusID In (2,3)
) as T
inner join 
(
	 Select distinct orderid from dbo._torderitems t1 where 
        	not exists 
		(
			 select * from dbo_t_products t2 where t2.productid = t1.productid and t2.instock=0
		)
) as T3
On T.OrderID = T3.OrderID

Chirag

http://chirikworld.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 05:51:16

Or this?

SELECT DISTINCT	a.*
FROM		dbo.t_Orders a
INNER JOIN	dbo.t_OrderItems b ON b.OrderID = a.OrderID
INNER JOIN	dbo.t_Products c ON c.ProductID = b.ProductID AND c.InStock <> 0
WHERE		StatusID IN (2, 3)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources