title: Postgresql excluding results based on subquery
body: <p>I have a postgresql query that looks like this:</p>
  
  <pre><code>SELECT cats.cat_name,
      cats.breed,
      cats.color,
      cats.gender,
      cats.shelter_location,
      microchip_num.id as microchip,
      release_num.release_num AS release_num,
      cat_files.file,
      max(adoption_num.adoption_num),
          CASE
              WHEN release_num.release_num ~~ 'R%'::text THEN '2'::text
              ELSE '1'::text
          END AS adoptability,
          CASE
              WHEN cats_age.age &lt; '12'::bigint THEN '1'::text
              WHEN cats_age.age &gt; '11'::bigint THEN '2'::text
              ELSE NULL::text
          END AS cat_age
     FROM cats
       JOIN microchip_num ON cats.fk_microchip = microchip_num.id
       JOIN release_num ON release_num.fk_cat_micro = cats.fk_microchip
       LEFT JOIN adoption_num ON adoption_num.fk_microchip_cats = cats.fk_microchip
       LEFT JOIN cats_deceased ON cats.fk_microchip = cats_deceased.cats_micro
       LEFT JOIN cats_returned ON adoption_num.id = cats_returned.adoption_num
       LEFT JOIN cat_files ON cats.fk_microchip = cat_files.fk_cats_key
       LEFT JOIN cats_age ON cats_age.fk_cats_micro = cats.fk_microchip
    WHERE cats.cat_name IS NOT NULL AND microchip_num.microchip !~~ 'null%'::text AND '1'::text =
          case
              WHEN (adoption_num.adoption_num IN ( 
                      select adoption_num.adoption_num 
                          from cats
                              join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                  except
                      select adoption_num.adoption_num 
                          from cats
                              join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                              join cats_returned on adoption_num.id = cats_returned.adoption_num)) 
                  THEN '0'::text
              ELSE '1'::text
          END AND cats.shelter_location = '1'::bigint AND cats_deceased.deceased = false
    GROUP BY cats.cat_name, cats.breed, cats.color, cats.gender, cats.shelter_location, microchip_num.microchip, cat_files.file, release_num.release_num, cats_age.age, microchip_num.id, adoption_num.adoption_num;
  </code></pre>
  
  <p>The problem I am having is with this section here:</p>
  
  <pre><code>case
              WHEN (adoption_num.adoption_num IN ( 
                      select adoption_num.adoption_num 
                          from cats
                              join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                  except
                      select adoption_num.adoption_num 
                          from cats
                              join adoption_num on cats.fk_microchip = adoption_num.fk_microchip_cats 
                              join cats_returned on adoption_num.id = cats_returned.adoption_num)) 
                  THEN '0'::text
              ELSE '1'::text
          END
  </code></pre>
  
  <p>To put this in simple terms I have table <strong>FOO</strong>, <strong>BAR</strong>, and <strong>BAZ</strong>. table <strong>FOO</strong> contains all available values, table <strong>BAR</strong> is a child of <strong>FOO</strong> and <strong>BAZ</strong> is a child table of <strong>BAR</strong>  </p>
  
  <p>a graphical demonstration:</p>
  
  <pre><code>FOO
   |
   |
   -----BAR
   |     |
   |     |
   -----------BAZ
  </code></pre>
  
  <p>How can I tell postgresql to pull all the values from <strong>FOO</strong> where <strong>BAR</strong> values are <em>NULL</em> but also where <strong>BAZ</strong> values are included, only if there are no additional values that appear in <strong>FOO</strong> that do not appear in <strong>BAZ</strong> </p>
  
  <p>example:</p>
  
  <pre><code>table **FOO**
  cars_id   car_type   
     1       Toyota
     2       Ford
     3       Sega
  table **BAR**
    id   sold    car_id_foreign_key (id of row from the *Foo* table) date_of_sale
    1     Yes           1                                              1/1/20
    2     yes           2                                              1/2/20
    3     yes           1                                              3/3/20
  table **BAZ**  
     Returned    date_returned sale_id_foreign_key (id of row from the *BAR* table*)
        yes           2/2/20         1
  </code></pre>
  
  <p>In this example all I would want to show in my query would be the <em>Sega</em> car since the <em>toyota</em> car was sold and returned but sold again and the <em>Ford</em> car was sold and never returned</p>