PostgreSQL-如何在一个表中获取另一个表中不匹配的条目 [英] Postgresql - how to get entries in a table that do not have a match in another table

问题描述

我有一个问题，但是我真的不知道该怎么问！

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

为解释以上内容，我们在一个称为 shipping_code的表中包含了运输（交付）代码，并且，由于-language网站，我们在 shipping_code_description中还有另一个表来描述这些代码。
我们还有一个名为 site_international的表，其中包含以下字段：'siteid'（网站的ID-例如

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

上面的英国，德国，法国（英语，德语，法语）和已发布（布尔字段，即该网站是否处于启用状态）？ SELECT查询将获取所有送货代码，以及仅针对那些已发布网站的语言描述。

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

现在，我们还可以蚂蚁知道哪些运输代码在某些站点中没有描述。如果运输代码是全新的，则将为该代码返回1行（由于LEFT OUTER JOIN）。

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.

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

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?

以下是我的选择：

1）我可以以某种方式在一个查询中完成全部操作。必须有一种方法（我以前从未使用过UNION，EXCEPT等，而且不确定是否应该使用这些东西。）

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）或者我可以简单地对
选择另一个siteid从site_international WHERE Published ='t'

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

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

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.

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

Please advise if a better option "1" exists?

非常感谢！

推荐答案

也许我听不懂您的问题，但是如果您想获取表中的条目与另一个表中的条目不匹配，我可以给你一个简单的代码段。

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

这篇关于PostgreSQL-如何在一个表中获取另一个表中不匹配的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！