title: SQL inclusion and exclusion
body: <p>The property listing table has a listing id (lid) and owner id (cid).</p>
  
  <p>The hasA, hasB, hasC tables are descriptors of the property. hasA holds Active Codes like: For Sale, For Rent etc. hasB holds Status Codes like: New Price, Just Listed, Under Contract etc. hasC holds Description Codes like: Single Family, Land, Farm, Waterfront, Mountain View, Log, etc. </p>
  
  <p>I do not foresee the use of exclusion in the hasA table but I do need it in the hasB and hasC tables.</p>
  
  <p>In this example I will just reference the numbers. Everything can be copied to sqlfiddle.</p>
  
  <pre><code>create table listings (
    lid integer, 
    cid integer 
  );
  
  create table hasA (
    lid integer,
    cid integer,
    id integer
   );
  
  create table hasB (
    lid integer,
    cid integer,
    id integer
   );
  
  create table hasC (
    lid integer,
    cid integer,
    id integer
   );
  
  
  insert into listings values
  (901,4),(902,4),(903,4),(904,4),(905,4),(906,4),(907,4),(908,5);
  
  insert into hasA values
  (901,4,333),(902,4,333),(903,4,333),(904,4,333),(905,4,333),(906,4,333),(907,4,444),(908,5,333);
  
  insert into hasB values
  (901,4,700),(901,4,707),(902,4,702),(902,4,701),(903,4,701),(904,4,708),(905,4,708);
  
  insert into hasC values
  (901,4,2000),(901,4,2001),(902,4,2000),(902,4,2003),(903,4,2000),(903,4,2001),(904,4,2015);
  </code></pre>
  
  <p>I need help building TWO queries. Possibly this has been answered but I did look through the existing solutions and was unable to apply other solutions to my problem. My SQL is basic.</p>
  
  <p>In the first query I am including all listings that have 333 in hasA, and 2001 in hasC. Using IN () has always worked fine for my results. Result set: 901, 903.</p>
  
  <pre><code>select a.lid 
    from listings a, hasA b, hasC c 
   where a.lid = b.lid 
     and a.lid = c.lid 
     and a.cid = b.cid 
     and a.cid = c.cid 
     and b.id in (333) 
     and c.id in (2001);
  </code></pre>
  
  <p>Now I am trying to get all the listings where hasC cannot include 2001. Based on visual deduction the desired result set is: 902, 904, 905, 906. My use of  NOT IN() below does not work.</p>
  
  <pre><code>select a.lid 
    from listings a, hasA b, hasC c 
   where a.lid = b.lid 
     and a.lid = c.lid 
     and a.cid = b.cid 
     and a.cid = c.cid 
     and b.id in (333) 
     and c.id not in (2001);
  </code></pre>
  
  <p>The next examples involve the same logic but includes the hasB and hasC table.</p>
  
  <p>Straight forward inclusion works fine and gives result set: 903</p>
  
  <pre><code>select a.lid 
    from listings a, hasA b, hasB c, hasC d 
   where a.lid = b.lid 
     and a.lid = c.lid 
     and a.lid = d.lid 
     and a.cid = b.cid 
     and a.cid = c.cid 
     and a.cid = d.cid 
     and b.id in (333) 
     and c.id in (701) 
     and d.id in (2001)
  </code></pre>
  
  <p>Now I am trying to get all the listings where hasC cannot include 2001. Based on visual deduction the desired result set is: 902. My use of  NOT IN() below does not work.    </p>
  
  <pre><code>select a.lid 
    from listings a, hasA b, hasB c, hasC d 
   where a.lid = b.lid 
     and a.lid = c.lid 
     and a.lid = d.lid 
     and a.cid = b.cid 
     and a.cid = c.cid 
     and a.cid = d.cid 
     and b.id in (333) 
     and c.id in (701) 
     and d.id not in (2001)
  </code></pre>
  
  <p>If my desired result set based on visual deduction is incorrect, please correct this.</p>