sql - 查询没有产生它应该产生的结果

Question

我有以下两个表:

表作者:

AuthorID  Name
A         John
B         Alex
C         Carl

表作者兼容性:

AuthorID1    AuthorID2    Compatibility
A            B            5.0
A            C            4.0
B            C            4.5

我想制作以下内容:

AuthorID1   Name1    AuthorID2   Name2   Compatibility
A           John     B           Alex    5.0
A           John     C           Carl    4.0
B           Alex     C           Carl    4.5

这是我尝试过的方法，但我知道它不起作用:

select AuthorID1, A.Name, AuthorID2, B.Name, Compatibility
from AuthorsCompatibility, Authors as A, Authors as B
where AuthorID1=A.AuthorID and AuthorID2=B.AuthorID

Answer 1

SELECT ac.AuthorID1, a.Name, ac.AuthorID2, b.Name, ac.Compatibility
FROM AuthorsCompatibility ac
INNER JOIN AUTHORS a ON ac.AuthorID1 = a.AuthorID
INNER JOIN AUTHORS b ON ac.AuthorID2 = b.AuthorID