用于匹配卖家和买家的 SQL 查询

好吧，我和人们坐在一张桌子上。一张桌子上放着赠送的物品，另一张桌子上放着人们想要的物品。

People:
Person_ID, Name

Giveaways:
Person_ID, Item_ID

Wishlist:
Person_ID, Item_ID

所以我想要一个为特定用户返回交换建议的查询。

因此，如果我想为 A 人交换建议，它应该返回一个人的列表，这些人正在赠送 A 人想要的物品，并且想要 A 人赠送的物品。 结果应包括：人 A 的物品、与之交换的人的姓名以及 id 和 item_ID。

@SérgioMichels 的答案应该是正确的。但它没有得到卖家的名字，而且它使用了（在我看来）应该避免的语法。

所以，这是一个替代方案......

SELECT
  buyer.name          AS buyer,
  buyerWants.name     AS buyer_wants,      (assuming the items have names),
  buyerHas.name       AS buyer_has,
  seller.name         AS seller,
  sellerWants.name    AS seller_wants,
  sellerHas.name      AS seller_has
FROM
  People              AS buyer
INNER JOIN
  Wishlist            AS buyerWants
    ON buyerWants.person_id = buyer.person_id
INNER JOIN
  Giveaways           AS sellerHas
    ON sellerHas.item_id = buyerwish.item_id
INNER JOIN
  People              AS seller
    ON seller.person_id = sellerHas.seller_id
INNER JOIN
  WishList            AS sellerWants
    ON sellerWants.person_id = seller.person_id
INNER JOIN
  GiveAways           AS buyerHas
    ON  buyerHas.item_id = sellerWants.item_id
    AND buyerHas.person_id = buyer.person_id
WHERE
  buyer.person_id = ?