MySQL从一张表的一行中获取多个结果

Question

有两个表“成本”和“联系人”。所有卖家和买家的姓名都在“联系人”表中。通过以下查询，我检索每件商品的卖家和买家的 ID，但我想从“联系人”表中获取他们的姓名

SELECT 
costs.id as ID,
costs.idContactPayedBy,
costs.idContactPayedTo

FROM costs

WHERE 
costs.idbuilding=286

但我想从联系人表中获取卖家和买家的姓名

SELECT 
costs.id as ID,
contacts.lastname as seller,
contacts.lastname as buyer

FROM costs , contacts

WHERE 
costs.idbuilding=286
and costs.idContactPayedBy = contacts.id
and costs.idContactPayedTo = contacts.id

所以想要的结果是这样的

ID  Seller   Buyer
21  jackson  Brown
29  Bush     wilson

Answer 1

SELECT 
c.id as ID,
cntby.lastname as seller,
cntto.lastname as buyer

FROM costs AS c 
INNER JOIN contacts AS cntby ON c.idContactPayedBy = cntby.id
INNER JOIN contacts AS cntto ON c.idContactPayedTo = cntto.id
WHERE c.idbuilding=286

注1:使用INNER JOIN仅当 idContactPayed[By/To]列是必填的 ( NOT NULL )。如果这些列允许空值，那么您应该使用 LEFT OUTER JOIN 。在我看来，这两栏都应该是必填的。

注 2:作为风格问题:请避免 old style joins (ANSI 86/89) :FROM table1 a, table2 b WHERE <join condition> .