Orders where ALL line items are discounted

Question

Post reply

Orders where ALL line items are discounted

RD-201664

Ten Centuries

Points: 1334
More actions
April 26, 2010 at 11:11 am

#99138

Hi,
Using the Northwind db as an example, how can you find orders where ALL lines items have a discount? The following shows only [Order Details] that have a discount but how do you see ONLY orders where ALL lines are discounted?
select o.OrderID, o.CustomerID, o.OrderDate, o.ShipVia
,d.ProductID, d.UnitPrice, d.Quantity, d.Discount
from Orders o
inner join [Order Details] d
on o.OrderID = d.OrderID
where d.Discount > 0
order by o.OrderID
--sample orders where all lines are discounted
10258
10269
10275
10279--has only 1 line item
10285
10291
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here is one option, unfortunately I can't test it as I don't have Northwind available.

select

o.OrderID,

o.CustomerID,

o.OrderDate,

o.ShipVia,

d.ProductID,

d.UnitPrice,

d.Quantity,

d.Discount

from

dbo.Orders o

inner join dbo.[Order Details] d

on o.OrderID = d.OrderID

where

not exists(select 1 from dbo.[Order Details] d1 where o.OrderID = d1.OrderID and d1.Discount = 0)

order by

o.OrderID

RD-201664 Ten Centuries Points: 1334 More actions · Answer 2

That works. I was trying to use count(*) to compare count of all lines with count of lines that have discounts. Just was not getting it. Your approach is simpler.

Thanks