Creating Active Record Query

[eluser]xtremer360[/eluser]
I'm trying to figure out how I can come up with this query to match the active record functions.

Code:

+---------------+               +---------------+               +---------------+
|   Title       |               | Title_champion|               |   Roster      |
+---------------+               +---------------+               +---------------+
|  id           | ------------< |  title_id     |      +------- |  id           |
|  title_name   |               |  contender_no |      |        |  roster_name  |
+---------------+               |  roster_id    | >----+        +---------------+
                                +---------------+              

SELECT t.title_name,
       CASE WHEN c.contender_no = 0 THEN 'Champion' ELSE 'Contender' END as champion,
       r.contender_no,
       r.roster_name
FROM title t
       INNER JOIN title_champions c ON t.id = c.title_id
       INNER JOIN roster r ON c.roster_id = r.id

Here's what I have so far.

Code:

/**
  * Get all title champions and contenders
  *
  * @return object
  */
    function get_title_champions()
    {
        $this->db->select($this->titles_table.'.title_name');
        $this->db->select($this->titles_table.'.id');
        $this->db->select($this->roster_table.'.roster_name');
        $this->db->select($this->titles_table.'.id');
        $this->db->from($this->titles_table);
        $this->db->join($this->roster_table, $this->titles_table.'.roster_id ='. $this->roster_table.'.id', 'inner');
        $this->db->join($this->title_champions_table, $this->titles_table.'.id ='. $this->title_champions_table.'.title_id', 'inner');
        $query = $this->db->get();
        
        if ($query->num_rows() > 0) return $query->result();
  return array();
    }