A bit complext query I suppose with lots of join
But this achieves the result
select r.reg_id,r.old_society_id,s.society_name,c.center,
r.new_society_id,s1.society_name,c1.center
from reg r left join society s
on (r.old_society_id = s.society_id) left join society s1
on (r.new_society_id = s1.society_id) left join center c
on(s.center_id = c.center_id) left join center c1
on(s1.center_id = c1.center_id)
where reg_id = 1
Not sure about performace. Some expert can comment probably but this will serve the purpose
Hope that helps. If it does, mark the answer as solution and/or upvote.
Thanks
Milind