一个查询中多个表上的多个左连接

I've got one master table, which has items stored in multiple levels, parents and childs, and there is a second table which may or may not have additional data. I need to query two levels from my master table and have a left join on my second table, but because of the ordering within my query this will not work.

我有一个主表，它有多个级别存储的项目，父项和子项，还有第二个表可能有也可能没有其他数据。我需要从我的主表中查询两个级别，并在我的第二个表上有一个左连接，但由于我的查询中的排序，这将不起作用。

SELECT something FROM master as parent, master as child
  LEFT JOIN second as parentdata ON parent.secondary_id = parentdata.id
  LEFT JOIN second as childdata ON child.secondary_id = childdata.id
WHERE parent.id = child.parent_id AND parent.parent_id = 'rootID'

The left join only works with the last table in the from clause, so I am only able to make it work for one of the left joins. In the example above none of the left joins will work because the first left join points towards the first table in the from clause, the second one will never work like this.

左连接仅适用于from子句中的最后一个表，因此我只能使其适用于其中一个左连接。在上面的示例中，左连接都不起作用，因为第一个左连接指向from子句中的第一个表，第二个连接将永远不会像这样工作。

How can I make this work?

我怎样才能做到这一点？

3 个解决方案

#1

This kind of query should work - after rewriting with explicit ANSI JOIN syntax:

这种查询应该工作 - 用显式ANSI JOIN语法重写后：

SELECT something
FROM   master      parent
JOIN   master      child ON child.parent_id = parent.id
LEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id
LEFT   JOIN second childdata ON childdata.id = child.secondary_id
WHERE  parent.parent_id = 'rootID'

The tripping wire here is that an explicit JOIN binds before "old style" CROSS JOIN with comma (,). I quote the manual here:

这里的跳闸是显式JOIN在“旧样式”CROSS JOIN与逗号（，）之前绑定。我在这里引用手册：

In any case JOIN binds more tightly than the commas separating FROM-list items.

在任何情况下，JOIN比分隔FROM-list项的逗号绑定得更紧密。

After rewriting the first, all joins are applied left-to-right (logically - Postgres is free to rearrange tables in the query plan otherwise) and it works.

在重写第一个之后，所有连接都是从左到右应用的（逻辑上 - Postgres可以*重新排列查询计划中的表），并且它可以正常工作。

Just to make my point, this would work, too:

为了说明我的观点，这也可行：

SELECT something
FROM   master parent
LEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id
,      master child
LEFT   JOIN second childdata ON childdata.id = child.secondary_id
WHERE  child.parent_id = parent.id
AND    parent.parent_id = 'rootID'

But explicit JOIN syntax is generally preferable, as your case illustrates once again.

但显式的JOIN语法通常更可取，正如您的案例再次说明的那样。

And be aware that multiple (LEFT) JOINs can multiply rows:

并注意多个（LEFT）JOIN可以乘以行：

Two SQL LEFT JOINS produce incorrect result
两个SQL LEFT JOINS产生不正确的结果

#2

You can do like this

你可以这样做

SELECT something
FROM
    (a LEFT JOIN b ON a.a_id = b.b_id) LEFT JOIN c on a.a_aid = c.c_id
WHERE a.parent_id = 'rootID'

#3

The JOIN statements are also part of the FROM clause, more formally a join_type is used to combine two from_item's into one from_item, multiple one of which can then form a comma-separated list after the FROM. See http://www.postgresql.org/docs/9.1/static/sql-select.html .

JOIN语句也是FROM子句的一部分，更正式地，join_type用于将两个from_item组合成一个from_item，其中多个from_item然后可以在FROM之后形成逗号分隔的列表。见http://www.postgresql.org/docs/9.1/static/sql-select.html。

So the direct solution to your problem is:

所以直接解决您的问题是：

SELECT something
FROM
    master as parent LEFT JOIN second as parentdata
        ON parent.secondary_id = parentdata.id,
    master as child LEFT JOIN second as childdata
        ON child.secondary_id = childdata.id
WHERE parent.id = child.parent_id AND parent.parent_id = 'rootID'

A better option would be to only use JOIN's, as it has already been suggested.

一个更好的选择是仅使用JOIN，因为它已被建议。

#1