[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();
}