Select with Join in Codeigniter

Question

I have a question in a select with the use of Join in codeigniter, follows:

I have 2 tables.

tabela jogo
id | id_time1 | id_time2
99 |    1     |    2

tabela time
id | time
1  | Real
2  | Barcelona

I want to return the teams to mount a confrontation: Real x Barcelona

My select is like this:

$this->db->select('jogo.*, time.time AS time_nome1, time.time AS time_nome2');
$this->db->from('jogo');   
$this->db->join('time', 'time.id = jogo.id_time1');     

This way I can return the team 1 but not the team 2 or vice versa, changing the Join to id_time2

How I do with Join to return the two teams?

Thanks in advance

Answer 1

Lacked a Join, follows the code:

$this->db->select('jogo.*, t1.time AS time_nome1, t2.time AS time_nome2');
$this->db->from('jogo');
$this->db->join('time as t1', 't1.id = jogo.id_time1');
$this->db->join('time as t2', 't2.id = jogo.id_time2');

The team names are in the same table, but each team has an ID, so each team is a row in the table. To bring both at the same time, you have to do two joins Inner. Note that I used t1 and t2 as alias and referenced these aliases in select (t1.time and t2.time). I hope I’ve helped.