sql - 内连接两次没有返回结果

Question

我有两个名为节点和链接的表，如下所示:

--Links:
-----------------------------------
id    fromx   fromy    tox     toy
-----------------------------------
a1      x1      y1      x2      y2
a2      x2      y2      x3      y3
a3      x2      y2      x4      y4
a4      x1      y1      x4      y4
a5      x1      y1      x5      y5

--Nodes:
id       x   y
--------------
1       x1  y1
2       x2  y2
3       x3  y3
4       x4  y4
5       x5  y5

我想通过将链接表中的 fromx、fromy 和 tox、toy 与节点表中的 x 和 y 进行匹配来生成第三个表，以生成如下表:

linkid  fromid  toid
--------------------
  a1     1       2
  a2     2       3
  a3     2       4
  a4     1       4
  a5     1       5

为了获得该结果，我使用此查询在节点表上使用以下查询连接两次，但没有得到任何结果。

select links.id as linkid, 
n1.id as nodeid, fromx, fromy, tox from links 
inner join nodes n1 
inner join nodes n2
on 
links.fromx = n1.x 
and  links.fromy = n1.y
and links.tox = n2.x
and links.toy = n2.y

如果有帮助的话，我很乐意创建一个临时表等。

Answer 1

select 
       l.id as link_id, 
       frm.id as from_id, 
       t.id as to_id
from  
       links l
inner join 
       nodes frm
       on frm.x = l.fromx
       and frm.y = l.fromy
inner join 
       nodes t
       on t.x = l.tox
       and t.y = l.toy

SQL Fiddle