多表连接 - 我可以为此添加外部连接吗? [英] Multi table joins - can I add an outer join to this?

问题描述

我在从外部联接工作的情况转移到失败的情况时遇到问题.

I'm having a problem moving from a situation where an Outer Join works, to where it fails.

工作(伪代码示例)

SELECT a.number, a.name, b.ref, c.ref, c.firmref
FROM jobs a, teams b LEFT OUTER JOIN teamfirms c ON b.ref = c.team
WHERE a.ref = b.job

工作和团队之间存在多对一的关系(每个工作有多个团队)，总是填充

There is a many to one relationship between jobs and teams (many teams per job) that is always populated

表 c 中可能有也可能没有公司，但上面的查询给出了我期望的结果(大约 5000 条记录)

There may or may not be firms in table c, but the query above gives me the result I would expect (approx 5000 records)

当我想从第四个表中引入有关团队的详细信息时出现问题

The problem comes when I want to bring in the details about the teams from a fourth table

我正在尝试的代码如下

SELECT a.number, a.name, b.ref, c.ref, c.firmref, d.name
FROM jobs a, teams b LEFT OUTER JOIN teamfirms c ON b.ref = c.team, firms d
WHERE a.ref = b.job
AND d.ref = c.firmref

此时，我试图捕获的 NULL 消失了，我删除了大约 500 条记录

At this point the NULLS that I am trying to capture disappear and I drop approx 500 records

我做错了什么?

推荐答案

试试这个.

select a.number, a.name, b.ref, c.ref, c.firmref, d.name
from jobs a left outer join teams b on b.job = a.ref
left outer join teamfirms c on b.ref = c.team
left outer join firms d on c.firmref = d.ref
left outer join table e on a.column = e.column

或者你可以这样做

select a.number, a.name, b.ref, c.ref, c.firmref, d.name
from
jobs a, teams b, teamfirms c, firms d
where
a.ref = b.job
and b.ref = c.team
and c.firmref = d.ref

一个或另一个......不是两个.

one or the other... not both.

只是为了更好地投入...

Just to throw this in for good measure...

您使用 INNER JOIN 返回所有行从两个表中都有一个比赛.IE.在结果表中所有行和列将有值.

You use INNER JOIN to return all rows from both tables where there is a match. ie. in the resulting table all the rows and colums will have values.

LEFT OUTER JOIN 返回所有行从第一个表，即使有第二个表中没有匹配项.

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN 返回所有行从第二个表，即使有第一个表中没有匹配项.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

这篇关于多表连接 - 我可以为此添加外部连接吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！