[thelist] Quick SQL Help
jason.handby
jason.handby at corestar.co.uk
Fri Jul 21 10:53:37 CDT 2006
Hi Rob,
> I need to isolate the following orders at which the whole
> thing came from the same warehouse (Per line item). Then the
> next thing is the opposite...all orders that didn't come from
> the correct warehouse. For
> example:
>
> Order# Ship Preferred
> 30000 TX OH
> 30000 OH OH
> 30001 WI WI
> 30002 CO CO
> 30002 DE DE
> 30003 NY TX
> 30003 TX TX
>
> The first report should return order numbers: 30001 and
> 30002. The second report should return order numbers 30000 and 30003.
>
> Any help on this brain tease would be appreciated,
You didn't say what your table was called, but suppose it's called
OrderLineItem. And you didn't say what database you were using, so I've
assumed Microsoft SQL Server.
For your first query you should be able to do:
SELECT DISTINCT OrderNumber FROM OrderLineItem
WHERE NOT EXISTS (SELECT * FROM OrderLineItem OLI2
WHERE OLI2.OrderNumber = OrderLineItem.OrderNumber
AND OLI2.Ship != OLI2.Preferred)
For your second one, just remove the "NOT", i.e.
SELECT DISTINCT OrderNumber FROM OrderLineItem
WHERE EXISTS (SELECT * FROM OrderLineItem OLI2
WHERE OLI2.OrderNumber = OrderLineItem.OrderNumber
AND OLI2.Ship != OLI2.Preferred)
HTH
Jason
More information about the thelist
mailing list