php - 连接来自 3 个表的记录

Question

我尝试连接具有以下架构的两个表中的记录

users
----
name
phone
email

sales
----
seller_id
buyer_id
amt
date

这是我的sql

Select
  sales.amt,
  sales.date,
  buyer.phone as buyer_phone,
  buyer.name as buyer_name,
  buyer.email as buyer_email,
  seller.phone As seller_phone,
  seller.name As seller_name,
  seller.email As seller_email
From
  users borrower Inner Join
  sales On borrower.id = sales.buyer_id Inner Join
  users seller On seller.id = sales.seller_id

但是我得到一条空记录

预期的输出应该是

amt,
date,
buyer_phone,
buyer_name,
buyer_email,
seller_phone,
seller_name,
seller_email

任何帮助或建议

Answer 1

如果将用于连接表 user 的表 sales 的两列不可为空，那么您当前的查询是正确的。但是，如果它们可为空，请使用 LEFT JOIN 而不是 INNER JOIN。

SELECT sales.amt,
       sales.date,
       buyer.phone as buyer_phone,
       buyer.name as buyer_name,
       buyer.email as buyer_email,
       seller.phone As seller_phone,
       seller.name As seller_name,
       seller.email As seller_email
FROM   sales
       LEFT JOIN users borrower 
          ON borrower.id = sales.buyer_id 
       LEFT JOIN users seller 
          ON seller.id = sales.seller_id

要进一步了解有关联接的更多信息，请访问以下链接:

• Visual Representation of SQL Joins