I have table role_relations where columns are referencing same another table called ‘roles’. Currently i’m joining twice. Is there better way where join once and derive ‘name’ ?

SELECT r1.name, r2.name FROM role_relations rr
JOIN roles r1 ON rr.id = r1.id
JOIN roles r2 ON rr.parent_id = r2.id
WHERE rr.role_relation_id = :rrId;

>Solution :

It is normal if you have hierarchy data to need to join the definitions twice. I doubt you have performance issues, as it’s common for security hierarchy (roles, groups) to perform such queries and basically the data is not huge (you have certain number of roles, not billions of them).

If you are concern about the double read of the data you can read the role_relations and roles once and build the output in the application.