How to get replace value of column with its matching foreign key in another table?

Viewed 22

So I have two tables. One is client that looks like this

+----------+-----------+----------+-------------+--------+---------+---------+----------------+------------+-------+---------+
| ClientId | FirstName | LastName | DateOfBirth | Gender | PriLang | SecLang | Address        | City       | State | ZipCode |
+----------+-----------+----------+-------------+--------+---------+---------+----------------+------------+-------+---------+
|    00002 | Peter     | Parker   | 2001-08-10  | Male   |       4 |       1 | 2389 Some Road | Where City | MA    | 60042   |
+----------+-----------+----------+-------------+--------+---------+---------+----------------+------------+-------+---------+

And the other is language

+-------------------+-----------------+
| LanguageId        | LanguageName    |
+-------------------+-----------------+
|                 1 | English         |
|                 2 | Spanish         |
|                 3 | Vietnamese      |
|                 4 | French          |
|                 5 | Korean          |
|                 6 | Japanese        |
|                 7 | Italian         |
+-------------------+-----------------+

I cannot figure out how to replace the foreign key values in PriLang and SecLang with the Language names from the language table.

1 Answers

You have to join the language table twice


Query #1

SELECT 
`ClientId`, `FirstName`, `LastName`, `DateOfBirth`, `Gender`
, l1.LanguageName 'PriLang', l2.LanguageName 'SecLang', `Address`, `City`, `State`, `ZipCode`
FROM client c
INNER JOIN 
language l1 ON c.PriLang = l1.LanguageId
INNER JOIN 
language l2 ON c.SecLang = l2.LanguageId;

| ClientId | FirstName | LastName | DateOfBirth | Gender | Address        | City       | State | ZipCode | PriLang | SecLang |
| -------- | --------- | -------- | ----------- | ------ | -------------- | ---------- | ----- | ------- | ------- | ------- |
| 2        | Peter     | Parker   | 2001-08-10  | Male   | 2389 Some Road | Where City | MA    | 60042   | French  | English |

View on DB Fiddle

Related