CODEWITHSUNDEEP
phpmysqlsql-servercodeigniterjoin
Randhir Singh
Randhir Singh

Reputation: 245

Codeigniter join two table based on two join conditions

I am implementing the join query with code-igniter. I have two tables

 1) Users -> Contains all users
 2) Query -> Contains queries assigned to users. Each query has two users to attend that query.

In Queries table I have two columns 

 1) attendingMD -> First user attending that query
 2) secondAttendingMD -> Second user that query.

I want to show the list of queries along with the name of both users attending that query. I managed to get the name if first user by using this code.

  $this->db->select("Query.*, Users.fullname as firstMD");
  $this->db->join('Users', 'Users.id = Query.attendingMD');
  $this->db->where('Query.isCompleted', 1);
  $query = $this->db->get('Query');
  return $query->result_array();

Please suggest me how can I achieve this. Here is the view of table I want to show the table.enter image description here

Upvotes: 0

Views: 2121

Answers (1)

rad11
rad11

Reputation: 1561

Try:

$this->db->select("Query.*, uf.fullname as firstMD, us.fullname as secondMD");
$this->db->join('Users uf', 'uf.id = Query.attendingMD');
$this->db->join('Users us', 'us.id = Query.secondAttendingMD');
$this->db->where('Query.isCompleted', 1);
$query = $this->db->get('Query');
return $query->result_array();

Upvotes: 6

Related Questions