从5个表中选择并显示结果

Question

1. 表：教师

ID_Teacher   Password   Id_Users  Name
--------------------------------------
1001         1234          1       A
1002         1234          2       B

2. 表：学生

Id_Student   Password   Id_Users  Name
--------------------------------------
52001         1234          3       C
52002         1234          4       D

3. 表：员工

Id_Employee   Password   Id_Users  Name  Country
--------------------------------------------------
60001         1234          5       E   NewYork
60002         1234          6       F   London

4. 表：商店

Id_Shop   Password   Id_Users    Name  Country
--------------------------------------------------
70001         1234          7       G   NewYork
70002         1234          8       H   London

5. 表：事务

ID_Transaction   Transaction_Of(Fk Id_Users)   Method    Recived_Transaction(Fk Id_Users)  Money   Status    TimeStamp
----------------------------------------------------------------------------------------------------------------------
1                          1                     Tranfer       5                           500     Sucess  2020-01-05 18:00:00
2                          5                     Tranfer       8                           500     Sucess  2020-01-05 18:00:00

我需要这个

结果：

ID_Transaction   Transaction_Of   Method    Recived_Transaction(Fk Id_Users)  Money   Status    TimeStamp
----------------------------------------------------------------------------------------------------------------------
1                          A      Tranfer       E               500     Sucess  2020-01-05 18:00:00
2                          E      Tranfer       H               500     Sucess  2020-01-05 18:00:00

我如何写查询？

Answer 1

这可能适用于

$result = $this->db->select("t.ID_Transaction, c.Name, t.Method, e.Name, t.Money, t.Status, t.TimeStamp")
->join("Teacher as c" , "t.Transaction_Of = c.Id_Users")
->join("Employee as e" , "t.Recived_Transaction = e.Id_Users")
->get("Table_Transaction as t")->result_array();