mysql - MySQL用左联接将多列匹配为一

This question already has answers here:

Why does this SQL code give error 1066 (Not unique table/alias: 'user')?
                                
                                    （4个答案）
                                
                        
                                3年前关闭。
            
                    
我有2张这样的桌子

name  | fNatId | mNatId
=======================
Smith |      1 |      1
Doe   |      1 |      0
Owen  |      0 |      2

和这个

id | countryName
================
 0 | U.S.
 1 | U.K.
 2 | Canada

我想将第一个表转换为此

Name    | Father's Nationality | Mother's Nationality
=====================================================
Smith   | U.K.                 | U.K.
Doe     | U.K.                 | U.S.
Owen    | U.S.                 | Canada

我该怎么做呢？使用两个LEFT JOIN引发Not unique table/alias错误。只使用一个将使这两个列匹配为父亲或母亲。

最佳答案

您需要为表使用唯一的别名。考虑您的第一个表为users，第二个表为countries，您是否尝试过这种方式？关注应该完全按照您的要求进行。

SELECT u.name as Name, c1.countryName as FathersNationality, c2.countryName as MothersNationality

FROM users as u

LEFT JOIN countries as c1 ON c1.id = u.fNatId

LEFT JOIN countries as c2 ON c2.id = u.mNatId

关于mysql - MySQL用左联接将多列匹配为一，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40646532/