starleaf1 starleaf1 - 15 days ago 5
MySQL Question

MySQL matching many columns into one with left join

I have 2 tables like this one

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


and this one

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


And I want to translate the first table into this

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


How do I do this? Using two
LEFT JOIN
's throws
Not unique table/alias
error. Using only one would match both the columns into either father's or mother's.

Answer

You need to use unique alias name for your table. Considering your first table as users and second table as countries, did you try this way ? Following should work exactly as you're looking for.

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
Comments