Postgresql - 如何在表中获取另一个表中没有匹配项的条目

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

提问by rishijd

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

为了解释上述内容，我们在一个名为“shipping_code”的表中有运输（交付）代码，并且，因为我们有一个多语言站点，我们在“shipping_code_description”中有另一个表用于这些代码的语言描述。我们还有一个名为“site_international”的表，它具有以下字段：“siteid”（站点的 ID - 例如对于 UK、DE、FR..（英语、德语、法语..）和“已发布”（布尔字段，即网站是否上线？）

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

上面的 SELECT 查询获取所有运输代码，仅包含这些已发布站点的语言描述。

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.

现在，我们还想知道哪些运输代码在某些站点中没有描述。如果运输代码是全新的，则该代码将返回 1 行（因为 LEFT OUTER JOIN）。'scd.siteid' 和 'scd.desc_frontend' 将为 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?

但是，如果存在英国（英语）站点的描述，但不存在 FR 和 DE 的描述，则上述查询将只返回一行，而不是三行。如何判断特定运输代码缺少 DE 和 FR 描述？

Here are my options:

以下是我的选择：

1) I could somehowdo 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).

1）我可以在一个查询中以某种方式完成这一切。一定有办法（我以前从未使用过 UNION、EXCEPT 等，我不确定这些是否是我应该使用的）。

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

2) 或者我可以简单地做另一个查询来 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.

以上将为我提供所有已发布的网站。然后，使用 PHP（我用它来编码我的网站），对于上述较大查询的每个结果，我会检查并查看是否缺少任何描述。例如，上面的 siteid 查询将返回 3 个 ID（UK、DE、FR）。然后，如果对于特定的运输代码只返回一个英国行，我就会知道缺少 DE 和 FR，我可以将此标记给我的客户。

Please advise if a better option "1" exists?

请告知是否存在更好的选项“1”？

Many thanks!

非常感谢！

回答by bartolo-otrit

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

回答by Tobsey

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.

我想这就是你要找的。Left Join 获取所有可能的 Shipping Code 和 Site Code 对，然后查找其中哪些在 Descriptions 表中没有条目。这就是 Exists 子句所做的。

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)