Detecting relationships among MySQL DB records

Question

Let's say I have 3 DB tables:

teachers, with the following fields:
- id
- name
subjects, with the following fields:
- id
- name
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'd like to get as output something like:

+----------+----------+---------------+
| Teacher1 | Teacher2 | Relationships |
+----------+----------+---------------+
| John     | Larry    |            10 |
| John     | Samantha |            12 |
| Samantha | Larry    |             9 |
| Ian      | Louis    |             3 |
+----------+----------+---------------+

And if I needed to retrieve which subjects made a relationship, we'd need to get something like:

+----+----------+----------+
| id | teacher1 | teacher2 |
+----+----------+----------+
|  1 | John     | Larry    |
|  2 | John     | Samantha |
|  3 | Samantha | Larry    |
|  4 | Ian      | Louis    |
+----+----------+----------+

And something like:

+-----+------------+
| id  |  subject   |
+-----+------------+
| 1   | math       |
| 1   | english    |
| 1   | science    |
| ... | ...        |
| ... | ...        |
| 4   | science    |
| 4   | literature |
| 4   | databases  |
+-----+------------+

Graphically, I'm representing this in a graph consulted over a web browser:

Graph from DB query

So when I hover one edge, it displays the relationships information.

I'll receive queries result over php programming, What such queries or stored procedure would produce me such required outputs?

In second table I get the teachers which have a relationship (subjects in common) and the number of relationships, each record got an ID. So in last table I get *which* are the relationships, what subjects teachers got in common. — diegoaguilar, Nov 25 '13 at 15:34

score 3 · Answer 1 · answered Nov 25 '13 at 16:03

3

Try this (You should load some sample data in SQLFiddle for better testing)

SELECT t1.`name` AS teacher1, t2.`name` AS teacher2, count(*)
FROM teachers AS t1
JOIN teachers AS t2
  ON t1.id > t2.id
JOIN teacher_subjects AS ts1
  ON ts1.teacher_id = t1.id
JOIN teacher_subjects AS ts2
  ON ts2.teacher_id = t2.id
    AND ts2.subject_id = ts1.subject_id
GROUP BY teacher1, teacher2
ORDER BY COUNT(*) DESC;

answered Nov 25 '13 at 16:03

AgRizzo

5,261
1
13
28

I like it, I couldn't figure out how to get rid of those records being repeated, but ur `ON t1.id > t2.id` did it :) – Kelu Thatsall Nov 25 '13 at 16:07
@KeluThatsall - thanks, our solutions are eerily close, down to the table aliases. The `>` had been someone else's solution while I had the `<>` like you on a problem a while ago :) – AgRizzo Nov 25 '13 at 16:16

Kelu Thatsall · Accepted Answer · 2013-11-25T16:52:20.553

1

These are my propositions for SQL.

Please remember, that every of these solutions is just a little modification from the previous ones.

First table

http://sqlfiddle.com/#!2/dc3f2/3

SELECT t1.name as 'Teacher1', t2.name as 'Teacher2', count(*) AS 'Relationships' 
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
GROUP BY ts1.teacher_id, ts2.teacher_id;

Second table

I think it's working now.

http://sqlfiddle.com/#!2/dc3f2/22

SELECT @rownum := @rownum + 1 as id, relations.Teacher1, relations.Teacher2
FROM (SELECT t1.name as 'Teacher1', t2.name as 'Teacher2'
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
GROUP BY ts1.teacher_id, ts2.teacher_id) as relations,
(SELECT @rownum := 0) r;

Third table

http://sqlfiddle.com/#!2/dc3f2/27

SELECT id, subject
FROM
(SELECT @rownum := @rownum + 1 as id, relations.Teacher1, relations.Teacher2, relations.id1, relations.id2
FROM (SELECT t1.name as 'Teacher1', t2.name as 'Teacher2', t1.id as 'id1', t2.id as 'id2'
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
GROUP BY ts1.teacher_id, ts2.teacher_id) as relations,
(SELECT @rownum := 0) r) as rel
JOIN
(SELECT t1.id as 'id1', t2.id as 'id2', s.name as 'subject'
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) as rel_subjects
ON rel.id1 = rel_subjects.id1 AND rel.id2 = rel_subjects.id2
ORDER BY id

On last thing. Please remember, that those pseudo keys for relationships are very changeable. That means that if you add new rows and remove some you will change those relationship's IDs. So not to get any errors you cannot use them in any storage column wherever in your database - just use them in SELECT queries.

edited Nov 25 '13 at 16:52

answered Nov 25 '13 at 16:02

Kelu Thatsall

2,494
1
22
50

Thanks, I'm checking it out. – diegoaguilar Nov 25 '13 at 16:08
Stupid question but I rather ask, what is the `@` in the query? – diegoaguilar Nov 25 '13 at 16:25
1

It is for variables. Here it's just used as a trick to generate pseudo keys for those rows. http://stackoverflow.com/questions/1971826/how-can-i-have-a-primary-key-in-a-view-a-key-that-doesnt-depend-on-source-tabl – Kelu Thatsall Nov 25 '13 at 16:27
Thanks! I think is more practical to assing an ID to relationships and generate a n<->n table indicating relationship id and subject. I'd really aprreciate your help – diegoaguilar Nov 25 '13 at 16:30
Wait, I think that's second table but it's showing the subjects ID under relationships field, right? – diegoaguilar Nov 25 '13 at 16:32
No, it counts the number of relations :) (on the first table) – Kelu Thatsall Nov 25 '13 at 16:33
What do you suggest for retrieving which relationships are actually present. Like I meant in third table? – diegoaguilar Nov 25 '13 at 16:35
@KeluThatsall see this [post](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) for detais to using < > – Imran Nov 25 '13 at 16:37
np, please test it out and tell me if there are some problems with it :) – Kelu Thatsall Nov 25 '13 at 16:45
Worked perfectly, is there any chance second table is actually including something like "Math, English, Science" I know it might out of SQL and relational DB theory but it will help out with php code. – diegoaguilar Nov 27 '13 at 02:27
For that you can use use `GROUP_CONCAT` function (when you use `GROUP BY` ofc) http://sqlfiddle.com/#!2/dc3f2/29 – Kelu Thatsall Nov 27 '13 at 07:38
I care more about the count than the ID. Like: Teacher1 Theacher2 Count Subjects – diegoaguilar Nov 27 '13 at 17:54
And by the way, THANKS SO MUCH – diegoaguilar Nov 27 '13 at 17:54
I rather say: dziękuję! – diegoaguilar Nov 27 '13 at 21:57
np :p That much you should be able to do yourself tho: http://sqlfiddle.com/#!2/dc3f2/32 – Kelu Thatsall Nov 27 '13 at 22:10

Detecting relationships among MySQL DB records

2 Answers2

Linked