MySQL 嵌套连接以及对非嵌套表字段的引用

Question

这些是（示例）表：

first:  id      abc

parent: id  childid  foo
         1        0       -- no  child
         2        1       -- one child

child:  id  abc  bar
         1
         2

last:   id  foo  bar

我想执行以下查询：

SELECT
  first.*
 ,parent.*
 ,child.*
 ,last.*

FROM        first  -- actually some joins

-- select exactly 1 `parent` row for each `first` row
-- with an optional `child` row (or NULLs if child.id=0)
LEFT JOIN ( parent
LEFT JOIN   child ON child.id = parent.childid
                 AND child.abc = first.abc <== ERROR HERE
          )       ON parent.childid = 0
                  OR child.id


-- conditions referring to the parent,child rows
LEFT JOIN   last  ON last.foo = parent.foo
                 AND last.bar = child.bar

不幸的是 MySQL 不喜欢嵌套连接中的外部引用：
“on Clause”中的未知列“first.abc”

如果有人可以帮助我修复此类查询，我会很高兴。

Answer 1

1. 失去那些括号！
2. ON Parent.childid = 0 OR child.id 无效。使用 ON Parent.childid in (0, child.id)

它应该如下所示：

...
LEFT JOIN  parent  -- removed opening bracket
LEFT JOIN   child ON child.id = parent.childid
                 AND child.abc = first.abc 
                 AND parent.childid = in (0, child.Id) -- removed closing bracket and used 'IN'
...   

Answer 2

请尝试以下查询：

SELECT first.*, parent.*, child.*, last.*
FROM first
LEFT JOIN parent ON parent.childid = 0 OR parent.childid IS NOT NULL
LEFT JOIN child ON child.id = parent.childid AND child.abc = first.abc
LEFT JOIN last  ON last.foo = parent.foo AND last.bar = child.bar