title: Stuck on SQL query with multiple joins
body: <p>Alright, the system I got is a pretty outdated ERP system based around an Ingres database. The database schema is ... well ... not very nice (not really normalized) but basically it works out. Please understand that I cannot change anything related to the database.</p>
  
  <p>Consider the following SQL statement:</p>
  
  <pre><code>SELECT
    -- some selected fields here
  FROM    
      sta_artikelstamm s
      left join sta_chargen c on c.artikel_nr = s.artikel_nr and c.lager != 93
      left join sta_artikelbeschreib b on s.artikel_nr = b.artikel_nr  and b.seite = 25 and b.zeilennr = 1
      left join sta_einkaufskonditionen ek on s.artikel_nr = ek.artikel_nr AND s.lieferant_1 = ek.kunden_nr
      left join sta_kundenstamm ks on ek.kunden_nr = ks.nummer AND ks.nummer = s.lieferant_1
      left join tab_teilegruppe2 tg2 on s.teilegruppe_2 = tg2.teilegruppe
  WHERE 
      (s.status = 0)
  AND 
      (s.teilegruppe_2 IS NOT NULL) AND (s.teilegruppe_2 != '') 
  </code></pre>
  
  <p>So far, this works as expected, I get exactely 40742 results back. The result set looks alright, the number matches about what I would expect and the statement has shown no duplicates. I explicitly use a LEFT JOIN since some fields in related tables may not contain entries but I would like to keep the info from the main article table nonetheless.</p>
  
  <p>Now, table tab_teilegruppe2 consists of 3 fields (bezeichnung = description, teilegruppe = part group == primary key, taricnr - please ignore this field, it may be null or contain some values but I don't need it).</p>
  
  <p>I though of adding the following SQL part to only include rows in the resultset which do NOT appear in a specific part group. I therefore added the following line at the very end of the SQL statement.</p>
  
  <pre><code>AND (s.teilegruppe_2 NOT IN (49,57,60,63,64,65,66,68,71,73,76,77,78,79,106,107))
  </code></pre>
  
  <p>I'm by no means an SQL expert (you probably have guessed that already), but shouldn't an additional WHERE statement remove rows instead of adding? As soon as I add this simple additional statement in the WHERE clause, I get 85170 result rows.</p>
  
  <p>Now I'm guessing it has to do with the "NOT IN" statement, but I don't understand why I suddenly get more rows than before. Anyone can give me a pointer where to look for my error?</p>