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










1















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.










share|improve this question
























  • 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












  • Which table does the id column in the result come from?

    – Barmar
    Nov 14 '18 at 23:19











  • 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 Minimal, Complete, and Verifiable example. PS Required to join 2 tables with their FKs in a 3rd table Is there any rule of thumb to construct SQL query from a human-readable description?

    – philipxy
    Nov 15 '18 at 0:25












  • I updated table with other number to be more understandable.

    – Boris J.
    Nov 15 '18 at 5:42















1















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.










share|improve this question
























  • 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












  • Which table does the id column in the result come from?

    – Barmar
    Nov 14 '18 at 23:19











  • 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 Minimal, Complete, and Verifiable example. PS Required to join 2 tables with their FKs in a 3rd table Is there any rule of thumb to construct SQL query from a human-readable description?

    – philipxy
    Nov 15 '18 at 0:25












  • I updated table with other number to be more understandable.

    – Boris J.
    Nov 15 '18 at 5:42













1












1








1








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.










share|improve this 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.







mysql relational-database parent-child junction-table






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 5:41







Boris J.

















asked Nov 14 '18 at 22:08









Boris J.Boris J.

518




518












  • 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












  • Which table does the id column in the result come from?

    – Barmar
    Nov 14 '18 at 23:19











  • 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 Minimal, Complete, and Verifiable example. PS Required to join 2 tables with their FKs in a 3rd table Is there any rule of thumb to construct SQL query from a human-readable description?

    – philipxy
    Nov 15 '18 at 0:25












  • I updated table with other number to be more understandable.

    – Boris J.
    Nov 15 '18 at 5:42

















  • 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












  • Which table does the id column in the result come from?

    – Barmar
    Nov 14 '18 at 23:19











  • 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 Minimal, Complete, and Verifiable example. PS Required to join 2 tables with their FKs in a 3rd table Is there any rule of thumb to construct SQL query from a human-readable description?

    – philipxy
    Nov 15 '18 at 0:25












  • I updated table with other number to be more understandable.

    – Boris J.
    Nov 15 '18 at 5:42
















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






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














Which table does the id column in the result come from?

– Barmar
Nov 14 '18 at 23:19





Which table does the id column in the result come from?

– Barmar
Nov 14 '18 at 23:19













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





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 Minimal, Complete, and Verifiable example. PS Required to join 2 tables with their FKs in a 3rd table Is there any rule of thumb to construct SQL query from a human-readable description?

– philipxy
Nov 15 '18 at 0:25






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 Minimal, Complete, and Verifiable example. PS Required to join 2 tables with their FKs in a 3rd table Is there any rule of thumb to construct SQL query from a human-readable description?

– philipxy
Nov 15 '18 at 0:25














I updated table with other number to be more understandable.

– Boris J.
Nov 15 '18 at 5:42





I updated table with other number to be more understandable.

– Boris J.
Nov 15 '18 at 5:42












1 Answer
1






active

oldest

votes


















1














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





share|improve this answer


















  • 1





    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










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53309466%2fmysql-how-to-join-junctional-table-to-another-junctional-table-with-values-fro%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














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





share|improve this answer


















  • 1





    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















1














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





share|improve this answer


















  • 1





    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













1












1








1







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





share|improve this answer













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






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 15 '18 at 9:50









Madhur BhaiyaMadhur Bhaiya

19.6k62236




19.6k62236







  • 1





    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












  • 1





    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







1




1





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





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



















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53309466%2fmysql-how-to-join-junctional-table-to-another-junctional-table-with-values-fro%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







這個網誌中的熱門文章

How to read a connectionString WITH PROVIDER in .NET Core?

In R, how to develop a multiplot heatmap.2 figure showing key labels successfully

Museum of Modern and Contemporary Art of Trento and Rovereto