I have two tables: student
, address
.
address includes id
and location
student includes id
, name
, roommate_id
, location_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?