search

Home  >  Q&A  >  body text

Retrieve nested related values ​​in mysql

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:

1

2

3

4

5

6

7

"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

2

3

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:

1

2

3

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粉007288593P粉007288593261 days ago536

reply all(2)I'll reply

  • P粉186904731

    P粉1869047312024-04-05 11:26:19

    1

    2

    3

    4

    5

    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:

    1

    2

    3

    4

    5

    SELECT greeting.value

    FROM person

    NATURAL JOIN language

    NATURAL JOIN greeting

    WHERE person.personId = ?

    reply
    0
  • P粉194541072

    P粉1945410722024-04-05 00:49:17

    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:

    1

    2

    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

    reply
    0
  • Cancelreply