0

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.

starleaf1
  • 2,701
  • 6
  • 37
  • 66

1 Answers1

2

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
Mahfuzul Alam
  • 3,057
  • 14
  • 15