postgresql join on join

Question

事务表(1行->1事务)

customer_code amount
A0BEFG        100
DEC21A        80

付款表(1行->1事务)

customer_id   payment_type
1             cash
2             credit_card

客户表(1行—>1 customer_code)

customer_code    customer_id
A0BEFG           2
DEC21A           1

预期输出:结合表

customer_code customer_id amount payment_type
AOBEFG        2           100    credit_card
DEC21A        1           80     cash       

换句话说，我的想法是将payment_type放入事务表，但是因为没有匹配的变量，所以我需要首先合并支付表和客户表，然后再将它们连接到事务表。

Code I've try:

with 
connection as (
select c.customer_code, c.customer_id, p. payment_type
from data.payment p
left join data.customer c on p.customer_id = c.customer_id 
),
transactions as (
select t.merchant_code, t.amount
from data.transactions t
)
select 
t.merchant_code, c.customer_id, c.amount, p.payment_type 
from transactions as t

代码是为PostgreSQL编写的

Answer 1

SELECT c.customer_code, c.customer_id, t.amount, p.payment_type
FROM customer AS c
INNER JOIN payment AS p ON p.customer_id = c.customer_id
INNER JOIN transactions AS t ON t.customer_code = c.customer_code

Answer 2

像这样将customer连接到其他表:

SELECT c.customer_code, c.customer_id, t.amount, p.payment_type
FROM transactions t
INNER JOIN customer c ON t.customer_code = c.customer_code
INNER JOIN payment p ON p.customer_id = c.customer_id

Results:

payment_type