Sql select statement with two foreign key taken from the same table

[eluser]Crackarjack[/eluser]

Code:

$this->db->select(
  'lead.username as username1,
      coor.username as username2,
      J.job_ID,
  J.project_name,
  P.payment_phase,
  E.email_password');
  
  $this->db->from('job_records  J', 'payment AS P', 'email_records AS E');
  $this->db->join('payment AS P', 'P.job_ID = J.job_ID');
  $this->db->join('email_records AS E', 'E.job_ID = E.job_ID');
  $this->db->join('users AS lead','J.job_user_lead_ID=lead.id');
  $this->db->join('users AS coor','J.job_user_coordinator_ID=coor.id');
  $result = $this->db->get();
  $test = $result->result();
  $str = $this->db->last_query();