Trending Posts

Optimize multiple join same table

Optimize multiple join same table

I have two tables: studentaddress.
address includes id and location
student includes idnameroommate_idlocation_id.

I need to find student’s info with student location and roommate location. I have used the query:

SELECT
student.name as name, stulocation.location as address,
roommate.name as roommate_name, rmatelocation.location as roommate_address
FROM student
LEFT JOIN student as roommate ON student.roommate_id = roommate.id
LEFT JOIN location as stulocation ON student.location_id = stulocation.id
LEFT JOIN location as rmatelocation ON roommate.location_id = rmatelocation .id

I have joined the same table location twice. Is there any better solution to it, instead of querying same table 2 time?