How to select all in a query but not certain items

• tinamiller1

  SSC-Addicted

  Points: 496

  More actions

  July 23, 2018 at 3:28 pm

  #407236

  I have the following columns and there are 807k rows in all so this is just example:

  Line of Business                           Product                                           Market
  Government                                  MMP                                                   VA
  Government                                  MMP                                                   CA
  Government                                  MMP                                                   TX
  Commercial                                   PPO                                                    VA
  Commercial                                   HMO                                                   CA
  Private                                           PPO                                                     TX

  I want to select all of the above except MMP in VA so my return would be everything else. As I said I have 807k rows and many more lines of business, same products of MMP, and the state of VA that appears for other lines of business and products. I was trying where (product = 'mmp' and market = 'va') but doing that excludes all VA and all MMP.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  July 23, 2018 at 4:46 pm

  #1998703

  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE Product<>'MMP' AND Market<>'VA';


• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 23, 2018 at 7:31 pm

  #1998707

  Joe Torre - Monday, July 23, 2018 4:46 PM

  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE Product<>'MMP' AND Market<>'VA';


  Should be
  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE Product<>'MMP' OR Market<>'VA';

  or
  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE NOT (Product = 'MMP' AND Market = 'VA')


  _____________
  Code for TallyGenerator

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 24, 2018 at 5:53 am

  #1998753

  Sergiy - Monday, July 23, 2018 7:31 PM

  Joe Torre - Monday, July 23, 2018 4:46 PM

  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE Product<>'MMP' AND Market<>'VA';


  Should be
  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE Product<>'MMP' OR Market<>'VA';

  or
  
SELECT [Line of Business], Product, Market
FROM [Table with unnecessary spaces in column names]
WHERE NOT (Product = 'MMP' AND Market = 'VA')


  My preference is for the last of those 2.   It makes it quite clear just from reading the query, exactly what you are looking for.   The first one requires that you understand that it's a logical equivalent.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)