mysql - How to join junctional table to another junctional table with values from parent tables?

Question

Didn't know how to correctly write a question in Title, but I will try to explain what I am trying to achieve.

These are my parent tables:

 hgs_meaning         hgs_word_types        hgs_transliterations
 +----+---------+    +----+-----------+    +----+-----------------+
 | id | meaning |    | id | word_type |    | id | transliteration |
 +----+---------+    +----+-----------+    +----+-----------------+
 | 3  | man     |    | 4  | noun      |    | 5  | mnjw            |
 +----+---------+    +----+-----------+    +----+-----------------+

These are my junctional tables:

 junc_meaning_word_type
 +----+------------+--------------+
 | id | meaning_id | word_type_id |
 +----+------------+--------------+
 | 2  | 3          | 4            |
 +----+------------+--------------+

 junc_transliteration_meaning_word_type
 +----+--------------------+----------------------+
 | id | transliteration_id | meaning_word_type_id |
 +----+--------------------+----------------------+
 | 1  | 5                  | 2                    |
 +----+--------------------+----------------------+

I know how to make SELECT JOIN query to get results for junc_meaning_word_type table but not for junc_transliteration_meaning_word_type

I know how to get this result:

 +----+-----------------+-------------------+
 | id | transliteration | meaning_word_type |
 +----+-----------------+-------------------+
 | 1  | mnjw            | 2                 |
 +----+-----------------+-------------------+

I am trying to achieve this result:

 +----+--------------------+------------+--------------+
 | id | transliteration_id | meaning_id | word_type_id |
 +----+--------------------+------------+--------------+
 | 1  | mnjw               | man        | noun         |
 +----+--------------------+------------+--------------+

How this can be done. I am guessing that I need to use nested SELECT queries (subqueries) or multiple JOINs, but I don't know how to construct such a query.

Here are my queries:

/* hgs_meanings and hgs_word_types join to junc_meaning_word_type */

SELECT junc_meaning_word_type.id, hgs_word_types.word_type, hgs_meanings.meaning
FROM junc_meaning_word_type
JOIN hgs_word_types ON junc_meaning_word_type.word_type_id = hgs_word_types.id
JOIN hgs_meanings ON junc_meaning_word_type.meaning_id = hgs_meanings.id 

/* hgs_transliterations and junc_meaning_word_type join to junc_transliteration_meaning */

SELECT junc_transliteration_meaning.id, hgs_transliterations.transliteration, junc_meaning_word_type.id
FROM junc_transliteration_meaning
JOIN hgs_transliterations ON junc_transliteration_meaning.transliteration_id = hgs_transliterations.id
JOIN junc_meaning_word_type ON junc_transliteration_meaning.meaning_word_type_id = junc_meaning_word_type.id

Any help would be appreciated.

your on the right track. A join can reference any previous table, it doesn't have to be the table immediately prior. Keep going with `JOINS` — danblack, Nov 14 '18 at 23:01
It would be easier to understand the example if you didn't have `id = 1` in all the tables. — Barmar, Nov 14 '18 at 23:24
Please use words to say what criterion a row meets to be the result based on the business situation and/or on what rows are in the base tables. Please give more representative data--that is very litte for us guess from. Please act on the rest of [mcve]. PS [Required to join 2 tables with their FKs in a 3rd table](https://stackoverflow.com/a/23842061/3404097) [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Nov 15 '18 at 00:25
I updated table with other number to be more understandable. — Boris J., Nov 15 '18 at 05:42

score 1 · Accepted Answer · answered Nov 15 '18 at 09:50

You can simply JOIN all the tables together with their appropriate Join relationships, and then SELECT the specific columns/expressions you require. You can also Alias the columns/expressions specified in the SELECT clause, to show a different name for them.

Again, it is advisable to use Aliasing on table name(s), in case of multi-table queries, for code clarity (readability) and avoiding ambiguous behaviour.

SELECT junctrans.id, 
       trans.transliteration AS transliteration_id, 
       mean.meaning AS meaning_id, 
       typ.word_type AS word_type_id 
FROM 
  junc_transliteration_meaning_word_type AS junctrans
JOIN junc_meaning_word_type AS juncmean 
  ON juncmean.id = junctrans.meaning_word_type_id 
JOIN hgs_transliterations AS trans
  ON trans.id = junctrans.transliteration_id 
JOIN hgs_meaning AS mean 
  ON mean.id = juncmean.meaning_id 
JOIN hgs_word_types AS typ 
  ON typ.id = juncmean.word_type_id

It works very well. Thank you very much. I will analyze your code to understand it better. — Boris J., Nov 15 '18 at 16:02

mysql - How to join junctional table to another junctional table with values from parent tables?

1 Answers1