Retrieve nested related values ​​in mysql

Question

I'm trying to retrieve related values ​​based on multiple entries in the database. I'm very new to MySQL in terms of using JOINs etc. and I'm trying to avoid involving PHP unnecessarily.

When I say "retrieve nested related values", look at the following example:

"Person" table          "Language" table          "Greeting" table

| personId | language | | languageId | greeting | | greetingId | value     | 
|----------|----------| |------------|----------| |------------|-----------|
| 1        | en       | | en         | 3        | | 1          | konichiwa |
| 2        | jp       | | jp         | 1        | | 2          | bonjour   |
| 3        | fr       | | fr         | 2        | | 3          | hello     |

If I want to retrieve the greeting of the first person , the process would be:

1 -> en -> 3      -- ID Flow
en -> 3 -> hello  -- Value flow
Person 1: "hello" -- Final result

Alternatively, if I wanted to retrieve the third person's greeting, it would be changed to:

3 -> fr -> 2        -- ID flow
fr -> 2 -> bonjour  -- Value flow
Person 3: "bonjour" -- Final result

So, how do I do this in MySQL? I apologize if this already has an answer; I can't seem to find the wording to research the correct answer.

Answer 1

SELECT greeting.value
FROM person
JOIN language ON person.language = language.languageId
JOIN greeting ON language.greeting = greeting.greetingId
WHERE person.personId = ?

Recommendation - Make relative column names equal. ie. Not language and languageId, but using the same name in both tables (e.g. using languageId). The same goes for the Greeting and greetingId columns. This will make the query simpler:

SELECT greeting.value
FROM person
NATURAL JOIN language
NATURAL JOIN greeting
WHERE person.personId = ?

Answer 2

JOIN joins records from two tables based on certain conditions. For example if you want to join the records in table "Person" with the records in table "Language" so that the value in column language is equal to the value in column languageId you can do this by giving Use the following FROM clause to do this:

FROM Person INNER JOIN Language 
  ON Person.language = Language.languageId

The result of this JOIN is a table that looks like this

Person.personId	Character.Language	Language.LanguageId	Language.greeting
1	one	one	3
2	Japan	Japan	1
3	fr	fr	2