使用3个表加入一对多关系

Good afternoon all -

大家下午好 -

I am struggling with a SSMS query. (SQL Server 2008)

我正在努力解决SSMS问题。 (SQL Server 2008)

I have three tables I need to join. Essentially I need data from Table 1 and Table 3, but need to use Table 2 to link all together.

我有三张桌子需要加入。基本上我需要表1和表3中的数据,但需要使用表2将所有数据链接在一起。

Table 1: Master ID, Name

表1:主ID,名称

Table 2: Master ID, Version ID

表2:主ID,版本ID

Table 3: Version ID, Calls

表3:版本ID,呼叫

Basically I am trying to look at ALL ROWS of Table 3, and include the NAME of the version from table 1. Everything I'm doing, is giving me a 'multi-part identifier "xxx" could not be bound."

基本上我正在尝试查看表3中的所有行,并包括表1中版本的名称。我正在做的一切,就是给我一个“多部分标识符”xxx“无法绑定”。

I've tried searching for these kinds of joins, and I'm just so new to this - I'm sorry.... I can't embed the screenshot of what I'm trying to accomplish; but it let me put a link to my screenshot. I'm hoping someone can help me with this join, and then the lightbulb will go on in my brain. Thank you in advance.

我已经尝试过搜索这些类型的连接,我只是对此很陌生 - 我很抱歉......我无法嵌入我想要完成的截图;但它让我给我的截图链接。我希望有人可以帮助我加入这个联盟,然后灯泡会在我的大脑中继续。先感谢您。

Requested result

2 个解决方案

#1

Seems pretty straight forward whats the issue?

看起来很直接的问题是什么?

ANSI 92 joins using inner join and on syntax. Using what appear to be keys for the tables.

ANSI 92使用内连接和语法连接。使用似乎是表的键。

SELECT T1.Name, T3.VersionId, T3.Calls
FROM Table1 T1
INNER JOIN table2 T2
 on T1.MasterID = T2.MasterID
INNER JOIN table3 T3
 on T2.VersionId = T3.VersionID

T1, T2, and T3 are table aliases.

T1,T2和T3是表别名。

The INNER JOIN means we only want records if they exist in both tables

INNER JOIN意味着我们只想要记录它们是否存在于两个表中

t1 and t2 join on MasterID

t1和t2加入MasterID

t2 and t3 join on VersionID

t2和t3加入VersionID

Maybe a right joins instead of inner if table 3 has records with versions not in table2; and your statement about ALL records from table3 is true. Then a right join would be needed instead of an inner; but in this case some records would not have a name!

如果表3具有不在table2中的版本的记录,则可能是右连接而不是内连接;并且您对table3中所有记录的陈述是正确的。然后需要右连接而不是内部连接;但在这种情况下,有些记录没有名字!

#2

Pretty straightforward:

SELECT T3.VersionId, T3.calls, T1.name
FROM Table3 T3
LEFT JOIN TABLE2 T2 ON T3.VERSIONID=T2.VERSIONID
LEFT JOIN TABLE1 T1 ON T1.MASTERID=T2.MASTERID

#1