Databases
StackOverflow2013
Postsdbo
POPostgresql - how to get entries in a table that do not have a match in another table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgresql - how to get entries in a table that do not have a match in another table
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload