starleaf1 starleaf1 - 1 year ago 72
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
's throws
Not unique table/alias
error. Using only one would match both the columns into either father's or mother's.

Answer Source

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 as Name, c1.countryName as FathersNationality, c2.countryName as MothersNationality

FROM users as u

LEFT JOIN countries as c1 ON = u.fNatId

LEFT JOIN countries as c2 ON = u.mNatId
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download