how to join four table to display specific data
我在加入表格时遇到了一些麻烦。我有四张桌子
“设备”表
|
1
2 3 4 |
equipment_id equipment_name
———– ————– 1 Motor Cycle 2 Private Car |
==================================
这是我的项目表
|
1
2 3 4 |
project_id project_name
———- ———— 1 xyz trading 2 abc private ltd |
=================================
这是出租桌
|
1
2 3 4 5 |
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 |
这是删除表
|
1
2 3 4 5 6 |
我想这样显示..(使用 Codeigniter)
设备2日志
=======
|
1
2 3 4 |
控制器传递给模型
|
1
|
$data[‘eqipment_details’]=$this->Equipment_model->getLog($equipment_id);
|
请帮我查询
- “SN”来自哪个列和表？
- SN 只是为了计算结果，使用 $count=1; $count=$count 1;在视野中
- 我在我的模型中使用了这个查询，我知道这是不正确的 public function getLog($equipment_id=0) { return $this->db->select( \\’*\\’ ) ->from(\\’ rent AS r \\ ‘) ->join(\\’设备 AS e\\’, \\’e.eq_id = r.eq_id\\’) ->join(\\’移除 AS m\\’, \\’m.eq_id = e.eq_id\\’) ->join(\\’project AS p\\’, \\’p.project_id = r.project_id\\’) ->where(\\’r.eq_id\\’, $eq_id) ->get()->result_array(); }
不得不猜测你的表名是什么，但你应该能够从中调整：
|
1
2 3 4 5 6 7 8 9 10 11 12 |
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; |
我强烈建议与您的命名约定保持一致，您将骆驼大小写和标题大小写与下划线混合。
- 没问题。您能否将问题标记为已回答，谢谢:)
来源：https://www.codenong.com/52907492/
