title: Implement complement of rule in decision table SQL
body: <p>I have  a query to make decisions based on multiple criteria using a decision table.</p>
  
  <p>Suppose here I need to know a result (Yes or No) depending on attributes a person has as shown in Table. </p>
  
  <pre><code>DROP    TABLE IF EXISTS `tbl_decision`; 
  CREATE TABLE `tbl_decision` (
    id_rule       INT NOT NULL primary key AUTO_INCREMENT,
    rule_name     VARCHAR(25) NOT NULL,
    minVal        INT NOT NULL,
    maxVal        INT NOT NULL,    
    decision      CHAR(1) NOT NULL,
    CONSTRAINT `uc_decision` UNIQUE (`id_rule`)           
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
  
  Insert into `tbl_decision` VALUES
  (1, 'WEIGHT'      , 60  ,100 ,'Y'),
  (2, 'HEIGHT'      , 0   ,170 ,'Y'),
  (3, 'Special rate', 10  ,30  ,'Y'),
  (4, 'Consumption' , 0   ,3300,'N');
  </code></pre>
  
  <p>Here is the table I am comparing to:</p>
  
  <pre><code>DROP    TABLE IF EXISTS `tbl_guy`; 
  CREATE TABLE `tbl_guy` (
    id_guy    INT NOT NULL primary key AUTO_INCREMENT,
    guy_name     VARCHAR(25)  NOT NULL,
    weight        INT NOT NULL,
    height        INT NOT NULL,    
    rate          INT NOT NULL,    
    consumption   INT NOT NULL,    
    CONSTRAINT `uc_guy` UNIQUE (`id_guy`)           
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
  
  
  Insert into `tbl_guy` VALUES
  (1, 'John'      , 200,220,10,1000),
  (2, 'Mary'      , 100,160,11,5100),
  (3, 'Diana'     , 80 ,155,19,4000),
  (4, 'Ruthanne'  , 82 ,165, 5,2000);
  </code></pre>
  
  <p>The decision is determined by the following query.</p>
  
  <pre><code>SELECT *  FROM 
  (
   SELECT id_guy,decision,
  CASE WHEN rule_name = 'WEIGHT'       THEN tbl_guy.weight
       WHEN rule_name = 'HEIGHT'       THEN tbl_guy.height
       WHEN rule_name = 'Special rate' THEN tbl_guy.rate
       WHEN rule_name = 'Consumption'  THEN tbl_guy.consumption
  END AS testQnty, rule_name, minVal, maxVal
  FROM tbl_guy CROSS JOIN tbl_decision
  ) t 
  WHERE minVal &lt;= testQnty AND testQnty &lt; maxVal 
  ORDER BY id_guy;
  </code></pre>
  
  <p>I am using min and max values to know if decision is Yes, However I do not know how to define a opposite rule. For instance I define:</p>
  
  <p>(3, 'Special rate', 10  ,30  ,'Y')</p>
  
  <p>to be yes if guy value on field rate is between 10 and 30</p>
  
  <p>but on field consumption if It is between 0 and 3300 decision is NO</p>
  
  <p>(4, 'Consumption' , 0   ,3300,'N');</p>
  
  <p>I know you can always make opposite rules using complement so
  if  <code>0 &lt;= x &lt;= 3300</code>  would become  <code>x &lt; 0 AND x &gt; 3300</code>
  but how to enable that on query?</p>
  
  <p><a href="http://sqlfiddle.com/#!2/40606/9" rel="nofollow">Here is my fiddle</a> </p>
  
  <p><a href="http://sqlfiddle.com/#!2/40606/15" rel="nofollow">other</a></p>