Retrieve nested related values ​​in mysql
P粉007288593
P粉007288593 2024-04-04 17:29:06
0
2
405

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.

P粉007288593
P粉007288593

reply all(2)
P粉186904731
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 = ?
P粉194541072

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
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!