title: How do you compare records within the multiple tables but look for different or missing values
body: <p>Here is a version of my Table:</p>
  
  <pre><code>p.productid | pp.productid|  pp.productpricingid | p.erpsku | pp.countrycode | pp.productstatus
   1            1                    1             AAAAA        US             Active
   2            2                    2             BBBBB        US             Active
   3            3                    3             CCCCC        US             Active
   4            4                    4             DDDDD        US             Active
  
   1            1                    5             AAAAA        CA             Active
   2            2                    6             BBBBB        CA             Active
   3            3                    7             CCCCC        CA             InActive
  </code></pre>
  
  <p>So I have 2 tables products, and product pricing. I need help writing a query that will pull all the values that are Active in US but inactive or nonexistant in CA. So for the example above the valeus CCCCC and DDDDD should be pulled.  The tricky thing is that all this data lives in both the product and productpricing tables, which I combine to make one data set. Then within that dataset I have to write another nested query where I find all the differences. Right now I can find all the values that are not in the US table, but Im not sure if this query is accurate. </p>
  
  <p>Here is a sample of my Query:</p>
  
  <pre><code>select p.erpsku from
  
  product p, productpricing pp
  
  where
  
  p.productid = pp.productid
  
  and pp.countrycode = 'us'
  
  and pp.productstatus = 'Active'
  
  and p.productid not in (select p.productid from product p, productpricing pp where p.productid = pp. productid and 
  
  pp.countrycode = 'CA' )
  </code></pre>
  
  <p>However I am unsure if this query is accurate at all. I would appreciate any help I could get with this.</p>