mysql - three joins on the same table

Question

I have two tables:

persons
- person_id
- fullname

students
- student_id
_ person_id
- father_id
- mother_id

In students table the last three columns store ids from persons table. What SELECT could retrieve the following data:

- student name
- father name
- mother name

We assume no WHERE, ORDER BY, or LIMIT for simplicity

Answer 1

try this:

select sp.fullname studentname, fp.fullname fathername, mp.fullname mothername
  from students s
 inner join persons sp on (s.student_id = sp.person_id)
 inner join persons fp on (s.father_id = fp.person_id)
 inner join persons mp on (s.mother_id = mp.person_id)

Answer 2

Try below query -

SELECT p.fullname,m.fullname,f.fullname from students s
    LEFT JOIN persons p ON s.person_id = p.id
    LEFT JOIN mother m ON s.mother_id = m.id
    LEFT JOIN father f ON s.father_id = f.id
    WHERE s.student_id = 'id of which student record you want';