多表连接 - 我可以添加一个外部连接吗？

发布于 2024-10-02 11:01:55 字数 629 浏览 5 评论 0原文

我在从外部联接有效的情况转移到失败的情况时遇到问题。

工作（伪代码示例）

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

工作和团队之间存在多对一关系（每个工作有多个团队），并且总是填充

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

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

我正在尝试的代码如下

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 条记录

我做错了什么？

原文

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

Working (pseudo code example)

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

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

The code I am trying is below

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

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

What am I doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江心雾 2024-10-09 11:01:55

对此进行打击。

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

其中之一...而不是两者都做。

只是为了更好地衡量这一点......

您使用 INNER JOIN 返回所有行
从两个表中都有一个
匹配。 IE。在结果表中所有
行和列都有值。
LEFT OUTER JOIN 返回所有行
从第一个表开始，即使有
第二个表中没有匹配项。
RIGHT OUTER JOIN 返回所有行
从第二个表来看，即使有
第一个表中没有匹配项。

take a whack at this.

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

or you could do

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...

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 returns all the rows
from the first table, even if there
are no matches in the second table.
RIGHT OUTER JOIN returns all the rows
from the second table, even if there
are no matches in the first table.

回复收藏 0 原文

平安喜乐 2024-10-09 11:01:55

您正在混合 ANSI 89 和 92 JOIN 语法（隐式和显式 JOIN）。尝试将整个查询转换为显式联接。问题可能是您添加的新 JOIN（隐式语法）是 INNER 并且想要成为 OUTER，或者您想要以不同的顺序解析 JOIN（一旦将它们全部写为 OUTER，您可以使用括号来完成此操作）加入）

回复收藏 0 原文

睫毛溺水了 2024-10-09 11:01:55

尝试以下操作：

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
        LEFT OUTER JOIN firms d on c.firmref = d.ref
WHERE a.ref = b.job

如果有效，您可以尝试将第二个 LEFT OUTER 变成 INNER。当我需要这类东西时，我通常把它作为外部，这可能是错误的。

Try, the following:

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
        LEFT OUTER JOIN firms d on c.firmref = d.ref
WHERE a.ref = b.job

If it works, you could then try to turn the 2nd LEFT OUTER into an INNER. Possibly incorrectly I've generally left it as an outer when I've needed this sort of thing.

回复收藏 0 原文

浪菊怪哟 2024-10-09 11:01:55

这是我的尝试：

SELECT a.number, a.name, b.ref, c.ref, c.firmref, d.name 
FROM jobs a
 join teams b on (b.job = a.ref)
 LEFT OUTER JOIN teamfirms c ON (b.ref = c.team)
 LEFT OUTER JOIN firms d on (d.ref = c.firmref)

这会将所有工作加入团队，如果存在团队公司，那么还会带来公司详细信息。如果没有团队牢固的关系，你仍然会得到空值。

Here is my attempt:

SELECT a.number, a.name, b.ref, c.ref, c.firmref, d.name 
FROM jobs a
 join teams b on (b.job = a.ref)
 LEFT OUTER JOIN teamfirms c ON (b.ref = c.team)
 LEFT OUTER JOIN firms d on (d.ref = c.firmref)

This will join all jobs to team and if a teamfirm exist then also bring firm details. if no team firm relationship you still get your nulls.

回复收藏 0 原文

·深蓝 2024-10-09 11:01:55

请尝试以下操作：

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
LEFT OUTER JOIN firms d ON c.firmref = d.ref
WHERE a.ref = b.job

Try the following:

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
LEFT OUTER JOIN firms d ON c.firmref = d.ref
WHERE a.ref = b.job

回复收藏 0 原文

~没有更多了~