mysql - SQL 内连接结果不正确

Question

我的问题如下:

我正在尝试使用 WordNet MySQL，尝试在现有 View 之外添加另一个 View ，例如:

CREATE OR REPLACE VIEW synsetsXsemlinksXsynsets AS 
SELECT 
       linkid,s.synsetid AS ssynsetid
       ,s.definition AS sdefinition
       ,d.synsetid AS dsynsetid
       ,d.definition AS ddefinition 
FROM synsets AS s 
     INNER JOIN 
     semlinks AS l ON s.synsetid = l.synset1id 
     INNER JOIN 
     synsets AS d ON l.synset2id = d.synsetid;

因此，以该代码作为引用，我尝试执行以下操作:

CREATE VIEW graph AS  
SELECT 
       l.linkid
       ,s.synsetid AS sssinsetid
       ,w.lemma AS swlemma
       ,s.definition AS sdefinition
       ,d.synsetid AS dsynsetid
       ,w.lemma AS dwlemma
       ,d.definition AS ddefinition 
FROM synsets AS s 
     INNER JOIN  
     semlinks AS l ON s.synsetid = l.synset1id 
     INNER JOIN  
     synsets AS d ON l.synset2id = d.synsetid 
     INNER JOIN  
     lexlinks AS x ON l.synset1id = x.synset1id 
                      AND l.synset2id = x.synset2id 
     INNER JOIN  
     words AS w ON w.wordid = x.word1id 
                      AND x.word2id = w.wordid

为了获得:linkid | ssynsetid |斯莱玛 |定义| dsynsetid | dsynsetid |困境| ddefition.

但它并没有按预期工作。虽然第一个给了我 285639 行，但我的只给了我 12 行。我不确定问题出在哪里。有什么帮助吗？

编辑:

我期望发生的是该语句将返回 sslemma 中的单词及其 dwlemma 中的引理。所以我希望我能得到所使用的单词和它们的引理形式。谢谢。

Answer 1

对 2 个额外的表使用 LEFT JOIN。

 ...
 FROM synsets AS s 
 INNER JOIN  
 semlinks AS l ON s.synsetid = l.synset1id 
 INNER JOIN  
 synsets AS d ON l.synset2id = d.synsetid 
 LEFT JOIN  
 lexlinks AS x ON l.synset1id = x.synset1id 
                  AND l.synset2id = x.synset2id 
 LEFT JOIN  
 words AS w ON w.wordid = x.word1id 
                  AND x.word2id = w.wordid

因为如果使用 INNER JOIN 那么它会过滤那些不匹配的。