如何联接四个表以显示特定数据

Question

我在连接表时遇到了一些麻烦。我有四张桌子

"设备"表

equipment_id   equipment_name
-----------    --------------
1              Motor Cycle
2              Private Car

====

==================================这是我的项目表

---

project_id    project_name
----------    ------------
1              xyz trading 
2              abc private ltd

====

==================================这是出租表

---

rental_id      project_id    equipment_id   Rental_date
---------------------------------------------------------
1                 2                 1         12/10/2018
2                 2                2          15/09/2018
3                 1                2          20/10/2018

这是移除表

---

remove_id      project_id    equipment_id   removal_date
---------------------------------------------------------
1                 2                 1         null
2                 2                2         14/10/18
these are my table, now i want to display data of single equipment (eg: equipment no 2) in which project this equipment ran, when was the rental date and when was the removal date from a project.

我想像这样显示..(使用代码点火器(

设备日志 2

=======

SN 项目名称 租赁日期 移除日期 ---  ------------      -----------       ------------ 1 ABC 私人有限公司 15/09/2018 14/10/2018 2 xyz 交易 20/10/2018 空

控制器传递到模型

$data['eqipment_details']=$this->Equipment_model->getLog($equipment_id);

请帮我查询

Answer 1

必须猜测您的表名是什么，但您应该能够从中进行调整：

SELECT
(@cnt := @cnt + 1) AS SN,
PROJECT.project_name AS Project Name,
RENTAL.Rental_date AS Rental date,
REMOVAL.removal_date AS Removal date 
FROM 
rental_table RENTAL 
LEFT JOIN project_table PROJECT ON (RENTAL.project_id = PROJECT.id) 
LEFT JOIN equipment_table EQUIPMENT ON (RENTAL.equipment_id = EQUIPMENT.id) 
LEFT JOIN removal_table REMOVAL ON (PROJECT.id = REMOVAL.project_id AND PROJECT.equipment_id = REMOVAL.equipment_id) 
WHERE 
EQUIPMENT.id = 2;

我强烈建议与您的命名约定保持一致，您将骆驼大小写和标题大小写与下划线混合在一起。