SQL: elegant way to get first, second, and third degree associations - Mysql

zeroes_ones
January 17, 2023
171 views
3 votes
3 Answers

I have tables named course, student and students_in in a MySQL database. The tables look like this:

course

  course_id       name
    3             Physics
   12             English
   19             Basket Weaving
    4             Computer Science
  212             Discrete Math
  102             Biology
   20             Chemistry
   50             Robotics
    7             Data Engineering

student

id    name
 2    Sally
 1    Bob
17    Robert
 9    Pierre
12    Sydney
41    James
22    William
 5    Mary
 3    Robert
92    Doris
 6    Harry

students_in

  course_id   student_id      grade
    3              2              B
  212              2              A
    3             12              A
   19             12              C
    3             41              A
    4             41              B
  212             41              F
   19             41              A
   12             41              B
    3             17              C
    4              1              A
  102              1              D
  102             22              A
   20             22              A
   20              5              B
   50              3              A
   12             92              B
   12             17              C
    7              6              A

Here is a Fiddle: http://sqlfiddle.com/#!17/8d86ee/34

My goal is to get the id and name of the students who:

have taken a course with Sally (i.e. "first-degree" relationship), OR
have taken a course with someone who has taken a course with Sally (i.e. "second-degree" relationship), OR
have taken a course with someone who has taken a course with someone who has taken a course with Sally (i.e. "third-relationship" relationship)

Essentially, we’re looking for first-, second-, and third-degree relationships to Sally.

Here is a depiction of what this looks like:

Since Sally took course IDs 3 and 212, the desired result would look like this (not the colorful table above, which I provided for illustration of the logic involved):

student_id       student_name
    12           Sydney             <-- took course ID 3 with Sally
    41           James              <-- took course ID 3 and 212 with Sally
    17           Robert             <-- took course ID 3 with Sally
     1           Bob                <-- took course ID 4 with James
    92           Doris              <-- took course ID 12 with James and Robert
   102           William            <-- took course ID 102 with Bob

I tried to solve this problem by using a Common Table Expression (CTE) to query the first-degree relationships, and can probably use two additional CTEs to get the second-degree and third-degree relationships. But, this feels like a very inelegant way to do this.

Can someone please help with an elegant approach to this problem?

Thank you!

Tags: mysql sql

Answers

You can use a recursive cte:

with recursive cte(cid, sid, name, l) as (
   select si.course_id, si.student_id, s.name, 1 from students_in si 
   join student s on s.id = si.student_id where si.course_id in (select si1.course_id 
      from students_in si1 join student s1 on s1.id = si1.student_id and s1.name = 'Sally') and s.name != 'Sally'
   union all
   select si.course_id, si.student_id, s.name, c.l + 1 from cte c 
   cross join students_in si
   join student s on s.id = si.student_id 
   where si.course_id in (select si1.course_id 
      from students_in si1 where si1.student_id = c.sid) and si.course_id  != c.cid and si.student_id != c.sid and c.l < 3
)
select distinct sid, name from cte where name != 'Sally'

See fiddle.

With Recursive coursemates As (
    Select y.student_id, 1 as removal
    From students_in x Inner Join students_in y
        On x.course_id=y.course_id
    Where x.student_id=2
  UNION
    Select y.student_id, r.removal+1
    From coursemates r Inner Join students_in x
        On r.student_id=x.student_id
      Inner Join students_in y
        On x.course_id=y.course_id
    Where removal<=2
)
Select c.student_id, min(c.removal) as howfar, min(s.name) as student_name
From coursemates c Left Outer Join student s
    On c.student_id=s.student_id
Where student_id <> 2
Group By c.student_id
Order by 2, 1

A little verbose, but also a little more generalized than your try in that you can control the depth.

A few defensive additions: 1. Left join on student table in case no R.I. there. 2. Filter out Sally from the result (don’t care that Robert was with Sally and then Sally was with Robert)

Join’s repeated as many times as needed, also good, but perhaps less elegant:)

with rel as ( --join student->student thru course
select t1.student_id s1Id, t2.student_id s2Id 
from students_in t1 inner join students_in t2 
  on t2.course_id=t1.course_id
where t2.student_id<>t1.student_id
group by t1.student_id,t2.student_id
)
,four as(
select  t1.s1Id as s1Id1 ,t2.s1Id s2Id1,t2.s2Id s2Id2 ,t3.s2Id s3Id2 
from rel t1 left join rel t2 on t2.s1Id=t1.s2Id and t2.s2Id<>t1.s1Id
 left join rel t3  on t3.s1Id=t2.s2Id and t2.s2Id<>t1.s1Id
      and t3.s2Id<>t1.s1Id
where  t1.s1Id=2  
group by t1.s1Id ,t2.s1Id,t2.s2Id,t3.s2Id
)
select t1.s1Id1,t1,s3Id2,s1.name,s3.name,s4.name,s6.name
from four t1
inner join student s1 on t1.s1Id1=s1.id
inner join student s3 on t1.s2Id1=s3.id
inner join student s4 on t1.s2Id2=s4.id
inner join student s6 on t1.s3Id2=s6.id

Please signup or login to give your own answer.

Click here to cancel reply.

SQL: elegant way to get first, second, and third degree associations – Mysql

Answers