这很简单

$this->db->select('mailsystem.*, users.username AS modtager, users.username as afsender')
$this->db->join('users', 'mailsystem.recipent = modtager.id AND mailsystem.sender = afsender.id', 'left')
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();

您是否尝试过在连接函数中强制使用别名（“AS”运算符在select子句中不起作用，因为您有......）？

<?php
$this->db->select('mailsystem.*, modtager.username AS modtager_name, afsender.username as afsender_name')
    ->join('`users` `modtager`', 'mailsystem.recipent = modtager.id', 'left')
    ->join('`users` `afsender`', 'mailsystem.sender = afsender.id', 'left');

$this->db->order_by('mailsystem.id', 'DESC');

return $this->db->get('mailsystem')->result();

使用别名如下-

$this->db->select('mailsystem.*, users_table_a.username AS modtager, users_table_b.username as afsender')
$this->db->join('users users_table_a', 'mailsystem.recipent = users_table_a.id', 'left');
$this->db->join('users users_table_b', 'mailsystem.sender = users_table_b.id', 'left');
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();

您可以使用以下命令：

$this->db->select('mailsystem.*, users.username AS modtager, users.username as afsender')
$this->db->join('users', 'mailsystem.recipent = modtager.id AND mailsystem.sender = afsender.id', 'left')
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();

如果您使用任何db前缀，请使用此

$this->db->select('mailsystem.*, users.username AS modtager, users.username as afsender')
$this->db->join('users', 'mailsystem.recipent = modtager.id AND '.$this->db->dbprefix('mailsystem').'.sender = '.$this->db->dbprefix('afsender').'.id', 'left')
$this->db->order_by('id', 'DESC');
return $this->db->get('mailsystem')->result();

Its very easy to get data from single table
$this->db->select('a.*,b.fname AS cname,c.fname as uname'); 
$this->db->from('tbl_menus a'); 
$this->db->join('tbl_admin_login b', 'b.id = a.create_by', 'left'); 
$this->db->join('tbl_admin_login c', 'c.id = a.update_by', 'left'); 
$this->db->order_by('a.id', 'desc'); 
return $this->db->get()->result_array();

在这个线程的时候，我认为codeigniter调用错过了在连接内部执行AS的可能性，因此这解决了这个问题：

$sql = "
    SELECT default_mailsystem.*,
           recipent.first_name AS modtager, 
           sender.first_name AS afsender
    FROM default_mailsystem
    LEFT JOIN default_profiles AS recipent ON recipent.id = default_mailsystem.id
    LEFT JOIN default_profiles AS sender ON sender.id = default_mailsystem.id
";
return $this->db->query($sql)->result();

你可以试试这个核心PHP

SELECT `custome_module`.`id`, `custome_module`.`name`, min(l1.nmark_completed) as call_waiter, min(l2.nmark_completed) as bill, min(l3.nmark_completed) as tray, min(l4.nmark_completed) as ordera
FROM `custome_module`
LEFT JOIN `restaurant_logs` as `l1` ON `custome_module`.`id` = `l1`.`nmodule_id` AND `l1`.`ntype` = 1
LEFT JOIN `restaurant_logs` as `l2` ON `custome_module`.`id` = `l2`.`nmodule_id` AND `l2`.`ntype` = 2
LEFT JOIN `restaurant_logs` as `l3` ON `custome_module`.`id` = `l3`.`nmodule_id` AND `l3`.`ntype` = 6
LEFT JOIN `restaurant_logs` as `l4` ON `custome_module`.`id` = `l4`.`nmodule_id` AND `l4`.`ntype` = 5
WHERE `custome_module`.`nbranch_id` = '142'
GROUP BY `custome_module`.`id`

还有在Codeigniter中

$this->db->select('custome_module.id, custome_module.name, min(l1.nmark_completed) as call_waiter, min(l2.nmark_completed) as bill,min(l3.nmark_completed) as tray,min(l4.nmark_completed) as ordera');
$this->db->from("custome_module");
$this->db->join('restaurant_logs as l1', 'custome_module.id = l1.nmodule_id AND l1.ntype = 1', 'left');
$this->db->join('restaurant_logs as l2', 'custome_module.id = l2.nmodule_id AND l2.ntype = 2', 'left');
$this->db->join('restaurant_logs as l3', 'custome_module.id = l3.nmodule_id AND l3.ntype = 6', 'left');
$this->db->join('restaurant_logs as l4', 'custome_module.id = l4.nmodule_id AND l4.ntype = 5', 'left');
$this->db->where('custome_module.nbranch_id', $this->data['user_session']['nid']);
$this->db->get();