0

I want to query data where BOTH the phone1 and phone2 codes don't contain the word 'mobile'. Tried AND and OR statements but its not working when 'MOBILE' exists in one of the fields. 

    select ORDCON.* from PL00.dbo.ordhdr inner join PL00.dbo.ordhdrx on ordhdrx.ORDER_NO = ordhdr.ORDER_NO
                               INNER JOIN PL00.dbo.schedule on schedule.ID_VAL = ordhdr.ORDER_NO
                              left outer join PL00.dbo.ordcon on ordcon.ORDER_NO = ordhdr.ORDER_NO
where ordhdr.RSPNS_CODE = 'TXTB4'
and (schedule.[DATE] >= CONVERT(varchar(10), getdate(), 101)) 
AND (NOT ORDCON.PHONE1CODE = 'MOBILE' 
OR NOT ORDCON.PHONE2 = 'MOBILE')
Mary Mahoney
  • 53
  • 2
  • 10
  • wouldn't that be "AND and AND" : AND (NOT ORDCON.PHONE1CODE = 'MOBILE' AND NOT ORDCON.PHONE2 = 'MOBILE') – MPavlak Apr 05 '17 at 22:41

1 Answers1

0

The logic you want is:

AND (ORDCON.PHONE1CODE <> 'MOBILE' AND ORDCON.PHONE2 <> 'MOBILE')

or, assuming neither is NULL:

AND 'MOBILE' NOT IN (ORDCON.PHONE1CODE, ORDCON.PHONE2)

Are you sure the second variable is not ORDCON.PHONE2CODE rather than ORDCON.PHONE2? I suspect the correct logic:

AND (ORDCON.PHONE1CODE <> 'MOBILE' AND ORDCON.PHONE2CODE <> 'MOBILE')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • wow, yes its supposed to be phone2code... i new this was supposed to work, just works better when you have correct data@! – Mary Mahoney Apr 05 '17 at 21:33