[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