Home > Software engineering >  Use inner join to get data from 5 different tables
Use inner join to get data from 5 different tables

Time:12-03

I'm using plain MySQL and I have the following problem:

  • Table A contains "seminars" (as ID) and "target_groups" (as ID)

  • Table B contains "seminars" (as ID) and "topics" (as ID)

  • Table C contains "ID" (which are seminars) and "title" (of seminars)

  • Table D contains "ID" (which are target groups) and "title" (of target groups)

  • Table E contains "ID" (which are topics) and "title" (of topics)

I need to capture all of them in one query. What I have so far:

select table_c.title as 'Seminars Title',
table_d.title as 'Target Groups Title'
from table_a 
inner join table_c on table_a.seminars=table_c.id 
inner join table_d on table_a.target_groups=table_d.id

so far this works fine.

But now I need to add Table B and Table E, too.As I cant get Table E through inner join with Table A, I need to use Table B. But I'm not sure how to do that?

CodePudding user response:

To include data from Tables B and E in your query, you can use additional INNER JOIN clauses to join those tables with Tables A and C, respectively. Here is an example of how you could modify your query to do this:

SELECT
  C.title AS 'Seminars Title',
  D.title AS 'Target Groups Title',
  E.title AS 'Topics Title'
FROM Table_A A
INNER JOIN Table_C C ON A.seminars = C.ID
INNER JOIN Table_D D ON A.target_groups = D.ID
INNER JOIN Table_B B ON A.seminars = B.ID
INNER JOIN Table_E E ON B.topics = E.ID

In this query, additional INNER JOIN clauses are used to join Table B with Table A on the seminars column, and to join Table E with Table B on the topics column. This allows the query to include data from all five tables in the final result set.

  • Related