postgresql - PostgreSQL - 如何使用四个表进行连接查询？

Question

我有这样的表：

code_number： ID、cnumber、名称、状态、program_id

id        cnumber    name     status     program_id
33051     1234561    ABC      NEW          32751
33052     1234562    XYZ      NEW          32751

口袋： ID、姓名、金额、类型、cnumber_id、program_id、customer_id

id       name      amount    type       cnumber_id program_id    customer_id
44546    personal  €1,40     PERSONAL    33051    32751         6612
44547    partner   €214,14   RECHARGE    33051    32751         6612
44548    discount            DISCOUNT    33051    32751         6612
51172    personal  €12,40    PERSONAL    33052    32751         6644
51173    partner   €54,50    RECHARGE    33052    32751         6644
51174    discount  €0,50     DISCOUNT    33052    32751         6644
51175    saving    €190,50   SAVING      33052    32751         6644

客户： ID、customer_name、program_id

id      customer_name       program_id
6612    shop1               32751
6644    shop2               32751

交易： ID、时间、金额、状态、类型、customer_id、cnumber_id、program_id、pocket_id

id      time                 type            status     amount  customer_id cnumber_id   program_id pocket_id
82420   16/05/202115:18:22  Saving          PROCCESSED  -€0,02  6612        33051      32751       null
82419   16/05/2021 15:18:22 Personal        PROCCESSED   €0,02  6612        33051      32751       null
82418   16/05/2021 15:18:22 ContraPayment   PROCCESSED   €0,30  6612        33051      32751       null
82417   16/05/2021 15:18:22 Payment         PROCCESSED  -€0,30  6612        33051      32751       null
82416   16/05/2021 13:12:25 Saving          PROCCESSED  -€0,02  6644        33052      32751       null
82415   16/05/2021 13:12:25 Personal        PROCCESSED   €0,02  6644        33052      32751       null
82414   16/05/2021 13:12:25 ContraPayment   PROCCESSED   €0,50  6644        33052      32751       null
82413   16/05/2021 13:12:25 Payment         PROCCESSED  -€0,50  6644        33052      32751       null

pocket_id 在交易列表中为空。如何使用这四个表实现以下列表？我需要为每笔交易显示 p_type 的查询。

TR_ID, Tr_Time, TR_type, TR_status, Tr_amount, customer_name, P_type, cnumber, program_id

我是创建查询的初学者，但我尝试了以下查询。我不能加入客户表，否则记录会加倍。

SELECT 
tr.id as tr_id,
tr.time as tr_date,  
tr.amount as tr_amount, 
tr.type as tr_type,
tr.status as tr_status, 
po.cnumber_id as cnumber_id, 
po.customer_id as customer_id, 
po.type as p_type,
cn.name as customer_name,
c.cnumber as cnumber,
tr.program_id as program_id from transaction tr
left join pocket po  on tr.pocket_id= po.id 
left join code_number c on c.id = po.cnumber_id 
left join customer cn on c.id = po.customer_id
where tr.program_id= 32751 and tr.status= 'NEW';

谢谢

在此处输入图像描述

标签： postgresqlleft-joininner-join