Postgresql - how to get entries in a table that do not have a match in another table

Question

I have a question, but I don't really know how to ask it! Please bear with me:

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') 

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?)

The above SELECT query gets all the shipping codes, with their language descriptions for those published sites only.

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.

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?

Here are my options:

1) I could somehow do this all within one query. There must be a way (I've never used UNION, EXCEPT, etc. before and I'm not sure if these are what I should use).

2) OR I could simply do another query to SELECT siteid FROM site_international WHERE published='t'

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.

Please advise if a better option "1" exists?

Many thanks!

Answer 1

Maybe I don't understand your question but if you want to get "entries in a table that do not have a match in another table" I can give you a simple snippet.

Select all rows from the left table which aren't in the right table.

SELECT  l.* FROM    t_left l LEFT JOIN t_right r ON      r.value = l.value WHERE   r.value IS NULL 

Answer 2

I think this is what you are looking for. The Left Join gets all possible Shipping Code and Site Code pairs, then it finds which of those don't have an entry in the Descriptions table. That is what the Exists clause does.

SELECT           Shipping_Code.*,     Site_International.SiteID  FROM             Shipping_Code      LEFT JOIN Site_International ON Site_International.Published = 't' WHERE     NOT EXISTS (         SELECT             NULL         FROM             Shipping_Code_Description         WHERE             Shipping_Code.ShippingID = Shipping_Code_Description.ShippingID         AND SiteInternational.SiteID = Shipping_Code_Description.SiteID)