SQL Server 2005:多个 INNER JOIN 表达式

发布于 2024-10-30 07:38:53 字数 734 浏览 5 评论 0原文

我在将多个表连接到主表时遇到问题。我的第一个连接是左外连接,它只是将两个表放在一起,形成整个视图的两半(为什么他们拆分表与安全性有关)。我在主表上有几个项目存储为 ID,例如product_id、application_id、state_id 和retailer_id。正如预期的那样,每个名称的名称都存储在它们自己的表中。

我的语句适用于 LEFT OUTER JOIN 和 2 INNER JOIN,但该语句在 3 INNER JOIN 时停止返回数据。我不知道我的 INNER JOIN 形成方式是否存在问题,或者什么:

FROM ((((MyDbo.warranty w
    INNER JOIN MyDbo.products p1 ON w.product_id = p1.product_id)
    INNER JOIN MyDbo.applications ap1 ON w.application_id = ap1.application_id)
    INNER JOIN MyDbo.retailers r1 ON w.retailer_id = r1.retailer_id)
    INNER JOIN MyDbo.states s1 ON w.state_id = s1.state_id)
    LEFT OUTER JOIN MyDbo.warranty_info i on w.warranty_id = i.warranty_id

我在这里偏离基地了吗?

I'm having trouble with joining several tables to a main table. My first join is a LEFT OUTER JOIN, and it simply puts two tables together for 2 halves of a whole view (why they split the table has something to do with security). I've got several items on the main table stored as IDs, such as product_id, application_id, state_id and retailer_id. The names of each of these are stored on their own tables, as expected.

My statement works fine with the LEFT OUTER JOIN and 2 INNER JOINs, however the statement stops returning data at 3 INNER JOINs. I don't know if there is a problem with how I've formed my INNER JOINs or what:

FROM ((((MyDbo.warranty w
    INNER JOIN MyDbo.products p1 ON w.product_id = p1.product_id)
    INNER JOIN MyDbo.applications ap1 ON w.application_id = ap1.application_id)
    INNER JOIN MyDbo.retailers r1 ON w.retailer_id = r1.retailer_id)
    INNER JOIN MyDbo.states s1 ON w.state_id = s1.state_id)
    LEFT OUTER JOIN MyDbo.warranty_info i on w.warranty_id = i.warranty_id

Am I way off base here?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

将军与妓 2024-11-06 07:38:53

如果 INNER JOIN 阻止您取回记录,则说明 JOIN 条件存在问题。

测试这一点的最佳方法是使用 LEFT JOIN 然后使用 WHERE 子句在右表中查找 NULL,例如:

SELECT *
FROM table t1
LEFT JOIN table2 t2
  ON t1.key = t2.key
WHERE t2.key IS NULL

或者您可以使用不存在的地方

SELECT *
FROM table t1
WHERE NOT EXISTS (SELECT NULL
                  FROM Table2 t2
                  WHERE t2.key = t1.key)

If INNER JOIN prevents you from getting records back, then you have an issue with the JOIN condition.

The best way to test for this is to use LEFT JOIN then a WHERE clause looking for NULL in the right table, like:

SELECT *
FROM table t1
LEFT JOIN table2 t2
  ON t1.key = t2.key
WHERE t2.key IS NULL

alternately you can use WHERE NOT EXISTS:

SELECT *
FROM table t1
WHERE NOT EXISTS (SELECT NULL
                  FROM Table2 t2
                  WHERE t2.key = t1.key)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文