Pretty Complex Query? - 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

General SQL Server Forums

New to SQL Server Programming

Pretty Complex Query?

Author

Topic

Wabby
Starting Member

27 Posts

Posted - 2012-04-07 : 09:27:40

Hi,

I have the following two tables:

Orders
OrderItems

Both are linked by Orders.ID

Each Order can contain many OrderItems.

I need to run a query to return:

ALL ORDERS that contain a specific product as one of the order lines for the order. But I want it to return ALL OrderItems when this criteria is matched. Each order can contain from 1 to 500 lines.

I am only interested in orders that DO NOT contain an OrderItem of "X"

Anybody nudge me in the right direction?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-07 : 09:34:52

Contains a specific product or DO NOT contain the specific product ?

quote:
ALL ORDERS that contain a specific product as one of the order lines for the order.

I am only interested in orders that DO NOT contain an OrderItem of "X"


select *
from   Orders o inner join orderitems i on o.ID = i.ID
where  exists (select * from orderitems x where x.ID = o.ID and x.OrderItem = 'the specific product')

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 18:04:44

your requirement seem contradictory to me as one place you say you want order with particular item and next line you say just the opposite!

here's another way anyways if its ORDERS that contain a specific product as one of the order lines for the order


SELECT *
FROM OrderItems oi
CROSS APPLY (SELECT o.*
             FROM Orders o1
             INNER JOIN OrderItems oi1
             ON oi1.ID = o.ID
             GROUP BY o.<required fields here>
             HAVING SUM(CASE WHEN oi.Product='your product' then 1 else 0 end) >0
            )tmp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Wabby
Starting Member

27 Posts

Posted - 2012-04-10 : 04:51:00

quote:
Originally posted by visakh16

your requirement seem contradictory to me as one place you say you want order with particular item and next line you say just the opposite!

here's another way anyways if its ORDERS that contain a specific product as one of the order lines for the order

SELECT *
FROM OrderItems oi
CROSS APPLY (SELECT o.*
FROM Orders o1
INNER JOIN OrderItems oi1
ON oi1.ID = o.ID
GROUP BY o.<required fields here>
HAVING SUM(CASE WHEN oi.Product='your product' then 1 else 0 end) >0
)tmp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks.

This works in 2005; any similair for 2000?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-10 : 12:34:55

[code]SELECT oi.*
FROM dbo.OrderItems AS oi
INNER JOIN (
SELECT OrderHeader
FROM dbo.OrderItems
WHERE Product = 'Your Product'
GROUP BY OrderHeader
) AS d ON d.OrderHeader = oi.OrderHeader[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources