php - 使用 userID 从另一个表中获取名字

Question

我有以下 mysql 表:表名:支付历史

journeyID | passengerID  | driverID 
---------------------------  
1         |    1         |3  
2         |    2         |4 

表名:用户信息

userID | firstName
------------------
1      |    ABC         
2      |    XYZ        
3      |    PQR         
4      |    MSN

我想要这样的结果

 journeyID | passengerName  | driverName 
------------------------------------------  
    1      |    ABC         |PQR  
    2      |    XYZ         |MSN 

Answer 1

这是一个简单的表连接:

select journeyID, pax.firstName as passengerName, dri.firstName as driverName 
    from PaymentHistory pay 
    join UserInformation pax on pay.passengerID = pax.userID 
    join UserInformation dri on pay.passengerID = dri.userID