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.
Recommendation - Make relative column names equal. ie. Not
language
andlanguageId
, but using the same name in both tables (e.g. usinglanguageId
). The same goes for the Greeting and greetingId columns. This will make the query simpler: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 columnlanguageId
you can do this by giving Use the following FROM clause to do this:The result of this JOIN is a table that looks like this