Joining same table using IDs

Question

I'm joining the admin_role table to the main table to display information.

I'm having a hard time joining a table to itself in Magento's syntax. I currently have

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel('admin/user_collection');
        $collection->getSelect()->join(
            array('ar' =>'admin_role'),
            'ar.user_id = main_table.user_id',
            array('parent_id')
        );

        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

And below that I have

protected function _prepareColumns()
    {
       $this->addColumn('Role', array(
            'header'    => Mage::helper('adminhtml')->__('Role'),
            'index'     => 'parent_id'
        ));
     ));

Here is a picture of my database table:

I want to join the admin_role table with itself so the people that have a parent_id of 35 match up with the role_id and display the name "Merchant".

I've always been bad at explaining database problems so let me know if you need any clarification!

Answer 1

I solved the issue I just double joined the tables.

$collection->getSelect()->join(
            array('ar' =>'admin_role'),
            'ar.user_id = main_table.user_id'
        ) ->join(
            array('ar2' => 'admin_role'),
            'ar.parent_id = ar2.role_id',
            array('role_name')
        );