mysql - 还选择空值

我有两个看起来像这样的桌子

使用者

id
name

贷款

id
borrower_id
lender_id
amt

我需要选择所有贷款，并在选择中匹配其名称，因此对于以下数据

使用者

id, name
1, mark
2, james

贷款

id, lender_id, borrower_id, amt
1,  1,         2,           100
2, 2,          1,           150
3, NULL,       1,           500
4, NULL,       2,           300

如果贷方未响应，则为NULL。输出应该是

loan_id, lender, borrower, amt
1,       mark,   James,   100
2,       James,  mark,    100
3,       --,     mark,   100
4,       --,     mark,   100

这就是我尝试过的

Select
  loans.amt As amt,
  borrower.name As borrower,
  lender.name As lender,
  loans.id AS loan_id
From
  (users borrower Join
  loans On borrower.id = loans.borrower_id) Join
  users lender On lender.id = loans.lender_id

最佳答案

您需要LEFT JOIN包括只有一个表具有符合条件的行的行。

尝试这个：

Select
  loans.amt As amt,
  borrower.name As borrower,
  lender.name As lender,
  loans.id AS loan_id
From
  loans
    LEFT JOIN users AS borrower ON loans.borrower_id = borrower.id
    LEFT JOIN users AS lender ON loans.lender_id = lender.id