title: Postgresql - how to get entries in a table that do not have a match in another table
body: <p>I have a question, but I don't really know how to ask it! Please bear with me:</p>
  
  <pre><code>SELECT      sc.*, 
          scd.siteid, scd.desc_frontend
  FROM        shipping_code sc
  LEFT OUTER JOIN shipping_code_description scd
      ON          scd.shippingid=sc.shippingid
      AND         scd.siteid IN (SELECT siteid FROM site_international WHERE published='t')
  </code></pre>
  
  <p>To explain the above, we have shipping (delivery) codes in one table called "shipping_code", and, because we have a multi-language site, we have another table for language descriptions of those codes, in 'shipping_code_description".
  We also have a table called "site_international" which has the following fields: 'siteid' (ID of the site - e.g. for UK, DE, FR..(english, german, french..) and 'published' (boolean field, i.e. is the site live or not?) </p>
  
  <p>The above SELECT query gets all the shipping codes, with their language descriptions for those published sites only.</p>
  
  <p>Now, we also want to know which shipping codes DO NOT have descriptions in certain sites. If a shipping code is totally new, then there will be 1 row returned for that code (because of the LEFT OUTER JOIN). The 'scd.siteid' and 'scd.desc_frontend' would be NULL.</p>
  
  <p>However, if a description for the UK (English) site exists, but the description for FR and DE do not exist, then the above query would just return ONE row, not THREE rows. How can I tell that the DE and FR descriptions are missing for a particular shipping code?</p>
  
  <p><strong>Here are my options:</strong></p>
  
  <p>1) I could <em>somehow</em> do this all within one query. There must be a way (<strong>I've never used UNION, EXCEPT, etc. before and I'm not sure if these are what I should use</strong>).</p>
  
  <p>2) OR I could simply do another query to 
      SELECT siteid FROM site_international WHERE published='t'</p>
  
  <p>and the above would give me all the published sites. Then, using PHP (which I'm using to code my site), for each result of the above larger query, I'd check and see if any descriptions are missing. E.g. The above siteid query would return 3 IDs (UK, DE, FR). Then if only one UK row is returned for a particular shippingcode, I'd know that DE and FR are missing, and I could flag this to my client. </p>
  
  <p>Please advise if a better option "1" exists?</p>
  
  <p>Many thanks!</p>