Joining MySQL query results

Question

After another question I've made before. I'd like to extend the number of tables being querying.

Situation was:

Let's say I have 3 DB tables:

1. teachers, with the following fields:
   • id
   • name
2. subjects, with the following fields:
   • id
   • name
3. teachers_subjects, with the following fields:
   • teacher_id
   • subject_id

So, a relationship comes some teachers have subjects in common.

I've thought about the algorithm for querying:

For each record s in subjects table:

Select the count in teachers_subjects table.
If count is greater than 1
In teacher_subjects, get teacher_id's where subject_id is s.

Make the relationship pairs.

I needed to get an output like:

+----------+----------+-----------------------+----------------------------+
| Teacher1 | Teacher2 | NumberOfRelationships |      SubjectsInCommon      |
+----------+----------+-----------------------+----------------------------+
| Ian      | Ralph    |                     4 | Math, Science, English, PE |
| Anna     | Maria    |                     2 | Math, Trigonometry         |
+----------+----------+-----------------------+----------------------------+

So I got the answer in this sql query:

SELECT @rownum := @rownum + 1 as id, relations.Teacher1, relations.Teacher2, relations.subjects, relations.count
FROM (SELECT t1.name as 'Teacher1', t2.name as 'Teacher2', GROUP_CONCAT(s.name) AS subjects, count(*) AS count
FROM teachers t1 
JOIN teachers t2 ON t1.id > t2.id
JOIN teachers_subjects ts1 ON ts1.teacher_id = t1.id
JOIN teachers_subjects ts2 ON ts2.teacher_id = t2.id AND ts1.subject_id = ts2.subject_id
JOIN subjects s ON ts1.subject_id = s.id
GROUP BY ts1.teacher_id, ts2.teacher_id) as relations,
(SELECT @rownum := 0) r;

But what if I have more tables like:

1. teachers, with the following fields:
   • id
   • name
2. subjects, with the following fields:
   • id
   • name
3. teachers_subjects, with the following fields:
   • teacher_id
   • subject_id
4. projects, with the following fields:
   • project_id
   • name
5. projects_subjects, with the following fields:
   • teacher_id
   • project_id
6. reasearches, with the following fields:
   • research_id
   • name
7. researches_subjects, with the following fields:
   • teacher_id
   • project_id

And I want to get an output like:

+----------+----------+-------------------------+--------------------+
| Teacher1 | Teacher2 | NumOfTotalRelationships | ActivitiesInCommon |
+----------+----------+-------------------------+--------------------+
|          |          |                         |                    |
+----------+----------+-------------------------+--------------------+

What if I need to add more tables in future, anyway it'll work for n tables?

Answer 1

You could try to create views for the low level relationships. And then you could join the views to obtain any relationship.