Multiple ids in a unique column each and joining them to get details of each on the same line

I’m having in one table a master_id, and student_id. I want to write a query that would print out both the names of the master and student on the same line while joining their details from another table.

master student table

id - 1
fullname - Derek Jason
type: master
id - 2
fullname - John Mason
type: student

assignments table

id - 1
from - 1
to - 2

So I’m trying to write a query that would return the results from the assignment table like

Results

id - 1
master- Derek
student - John Manson

I’ve tried this query but I’d need help to complete it.

SELECT name AS master FROM `assignments`
LEFT JOIN master_student ON assignments.from = master_student.id

Answer

I suspect you just want two joins:

SELECT a.id, ms.name AS master, mt.name as student
FROM assignments a LEFT JOIN
     master_student ms
     ON a.from = ms.id LEFT JOIN
     master_student mt
     ON a.to = mt.id;

Note: from and to are really, really bad column names because they are SQL keywords.