php - 我如何将表与第二个表的最新记录连接起来
问题描述
我有两个表,如注册表和 Academicdetails 表,根据注册 ID,我在学术表中有两条记录,但我想加入最新的学术表记录。
询问:
SELECT a.*, b.status AS ac_status
FROM registration AS a
LEFT JOIN academicdetails AS b ON a.id = b.registration_id
WHERE b.status = '1'
ORDER BY id DESC
LIMIT 100
PHP代码:
if(isset($search['category']) && $search['category']!=''){
$condition = ', TIMESTAMPDIFF(YEAR, a.dob, CURDATE()) AS age';
}
$this->db->select('a.*,b.status as ac_status'.$condition);
$this->db->from("registration as a");
$this->db->join('academicdetails as b','a.id = b.registration_id','left');
if(isset($search['search_by_name']) && $search['search_by_name']!=''){
$this->db->where('a.name', $search['search_by_name']);
}
if(isset($search['gender']) && $search['gender']!=''){
$this->db->where('a.gender', $search['gender']);
}
if(isset($search['status']) && $search['status']!=''){
$this->db->where('b.status', $search['status']);
}
if(isset($search['category']) && $search['category']!=''){
if($search['category']=='Youth'){
$this->db->where('age>10 AND age<18');
}elseif($search['category']=='Junior'){
$this->db->where('age>17 AND age<22');
}elseif($search['category']=='Senior'){
$this->db->where('age > 21');
}
}
if(isset($search['course']) && $search['course']!=''){
$this->db->where('b.course', $search['course']);
}
$this->db->limit($search['limit'], $search['start']);
$this->db->order_by($this->id, $this->order);
$this->db->group_by('a.id');
$query = $this->db->get();
解决方案
尝试这个:-
$this->db->select("a.*, b.status AS ac_status");
$this->db->from('registration AS a');
$this->db->join('academicdetails AS b', 'b.registration_id = a.id', 'left');
$this->db->where("b.status",1);
$this->db->order_by("b.id","DESC");//primary key or id of your academic table.
$this->db->limit(100);
$this->db->get();
推荐阅读
- javascript - 茉莉,业力,预期 0 为 1,'在找到的标签中找到'
- c# - GET 请求返回 400 错误响应
- abap - BAPI_GOODSMVT_CREATE 调用的强制参数?
- typescript - 泛型类型用作抽象类中的值
- html - 在反应而不是材料 UI 中使用 HTML 进行浮动按钮的问题
- icloud - CardDav(iCloud)补丁禁止响应的原因是什么?
- python - 在 numpy 数组中搜索
- cmake - CentOS 7 中的 cmake 版本比 CentOS 8 中的更新版本?
- webpack - NodeGit - 无法读取未定义的属性“类型”
- python - 如何正确设置 Django 模板路径?