Question: I'm attempting to retrieve a related value based on multiple entries made inside my database. I'm very new to **MySQL** in terms of using `JOIN`s and such and am trying to avoid involving **PHP** unnecessarily.

When I say *"retrieve nested related value"*, take 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 wanted to retrieve the greeting for **Person 1**, the flow would go:

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

```

Or, if I wanted to retrieve the greeting for **Person 3**, it would instead go:

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

```

So, how would I do this in **MySQL**? I apologise if this already has an answer; I cannot seem to get the wording to research the correct answer.

Answer: 
A JOIN joins records of two tables based ON some condition. E. g. you want to join the records in table "Person" with the records of table "Language", so that the value in column `language` is equal to the value in column `languageId` you can do this by giving the following FROM clause:

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

```

The result of this JOIN is a table which looks like

| Person.personId | Person.language | Language.languageId | Language.greeting |
| --- | --- | --- | --- |
| 1 | en | en | 3 |
| 2 | jp | jp | 1 |
| 3 | fr | fr | 2 |

Since it's a table you can JOIN it again with another table:

```
FROM Person 
  INNER JOIN Language ON Person.language = Language.languageId
  INNER JOIN Greeting ON Language.greeting = Greeting.greetingId

```

Apparently, this results in a table that looks like

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

From this you can select the desired columns, e. g. the greetings for the persons:

```
SELECT Person.personId, Greeting.value
FROM Person 
  INNER JOIN Language ON Person.language = Language.languageId
  INNER JOIN Greeting ON Language.greeting = Greeting.greetingId

```

The query results

| personId | value |
| --- | --- |
| 1 | hello |
| 2 | konichiwa |
| 3 | bonjour |

I've given qualified names (that is, including the table names) to make things more clear. This is not necessary, if the column names are unique.
```sql
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 columns names equal. I.e. not `language` and `languageId` but in both tables use the same name (for example use `languageId`). The same for greeting and greetingId columns. This will make the query more simple:

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

```