嵌套内部联接查询 [英] Nested Inner join query

问题描述

我有四个表，分别称为attr，data，extradata和syst.我必须进行多个/嵌套的内部联接才能从所有4个表中获取一些属性，并因此而遇到问题.在某些背景下，数据"表中有一列名为"ID"的列，我通过数据和额外数据之间的内部联接来获取该列，如下所示:

I have four tables called attr, data, extradata and syst. I have to do multiple/nested inner joins to get some attributes from all 4 tables, and running into issues because of that. For some background, there is a column called 'ID' in 'data' table that I am obtaining by an inner join between data and extradata as follows:

Select X.ID FROM data X 
INNER JOIN extradata XA 
ON X.dataID = XA.dataID 
WHERE X.data = 'condition1' and NOT XA.additionaldata = 'condition2'

此ID必须与attr表中的ID匹配，并且另外一个INNER与syst表联接.以下是我正在尝试的非常简短的查询版本:

This ID has to be matched with the ID in attr table, and one more INNER join with syst table. The following is a very abbreviated version of the query that's I'm currently trying out:

SELECT TOP(10) a.ID 
FROM attr AS a 
INNER JOIN data AS X ON a.ID = 
(
    Select X.ID FROM data X 
    INNER JOIN extradata XA 
    ON X.dataID = XA.dataID 
    WHERE X.data = 'condition1' and NOT XA.additionaldata = 'condition2'
)
INNER JOIN syst AS s ON a.sysID = s.sysID
WHERE s.desc = 'condition3'

(显然)我的查询出了点问题，对于任何建议，我将不胜感激.预先感谢！

There is something (obviously) wrong with my query, so I'd be grateful for any suggestions. Thanks in advance!

推荐答案

假设 attr.ID 映射到 data.ID ，您只需将所有表连接在一起所有条件都放在您的 WHERE 子句中:

Assuming attr.ID maps to data.ID, you can simply join all of the tables together and all of the conditions go in your WHERE clause:

SELECT TOP(10) a.ID 
FROM attr AS a 
    INNER JOIN data AS X ON a.ID = x.ID
    INNER JOIN extradata XA ON X.dataID = XA.dataID
    INNER JOIN syst AS s ON a.sysID = s.sysID
WHERE X.data = 'condition1' 
  and NOT XA.additionaldata = 'condition2'
  and s.desc = 'condition3'

将 attr 与 data 结合起来，还可以将 attr 与 extradata 结合起来，因为 data 成为所有3个之间的链接.

Joining attr to data allows you to also join attr to extradata, because data becomes the link between all 3.

这篇关于嵌套内部联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！