"if all items on the order are xyz, then extract" -- Please Help

• finance_teacher

  SSC Eights!

  Points: 855

  More actions

  November 3, 2004 at 6:23 pm

  #162547

  How can I write a "if all items on the order are RTD, then extract" ?

  i.e. The following would report only "P0123459" one time

  ------------------------------------------------

  OrderNumber ItemNumber

  P0123423 124536-RTD

  P0123423 12412-RTD

  P0123423 12127-RTD

  P0123423 12124-xgt

  P0123459 124536-RTD

  P0123459 12412-RTD

  P0123459 12127-RTD

  P0123459 12124-RTD

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  November 3, 2004 at 7:06 pm

  #528863

  Something like this?

  select distinct a.ordernumber

  from tblorder a

  where a.ordernumber not in

  (select distinct b.ordernumber

  from tblorder b

  where right(b.itemnumber,3) 'rtd')

  ---