retrieve nested related value in mysql-CodePudding

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 JOINs 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.

CodePudding user response：

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.

CodePudding user response：

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:

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