SQL查询以匹配卖家和买家

小编典典

SQL查询以匹配卖家和买家

sql

好吧，我和人在一起有一张桌子。一张桌子摆放着被赠予的物品，另一张桌子放着人们想要的物品。

People:
Person_ID, Name

Giveaways:
Person_ID, Item_ID

Wishlist:
Person_ID, Item_ID

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

因此，如果我想与人A交换建议，则应返回正在赠送人A想要的物品的人的清单，并希望得到人A所赠送的物品。结果应包括：人员A的项目，人员名称也要进行交换以及ID和item_ID。

阅读 160

2021-04-28

共1个答案

小编典典

@SrrgioMichels的答案应该是正确的。但是它没有得到卖方的名字，并且它使用的语法（在我看来）应该避免。

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

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 = ?

2021-04-28