SQL查询以匹配卖家和买家 [英] SQL query to match sellers and buyers

问题描述

好吧，所以我和别人一起坐了一张桌子.一张桌子上放着被赠予的物品，另一张桌子上放着人们想要的物品.

Okay so i got one table with people. One table with items that are given away and one with items people want.

People:
Person_ID, Name

Giveaways:
Person_ID, Item_ID

Wishlist:
Person_ID, Item_ID

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

So i want a query that returns exchange suggestions for a certain user.

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

So if i want exchange suggestions for person A it should return a list of people that are giving away an item that person A wants, and want an item that person A is giving away. The result should include: Person A's item, name of person too make exchange with as well as id and item_ID.

推荐答案

@SérgioMichels的答案应该是正确的.但是它没有得到卖方的名字，它使用的语法(在我看来)应该避免.

@SérgioMichels' answer should be correct. But it doesn't get the seller's name, and it uses syntax that should (in my opinion) be avoided.

所以，这是另一种选择...

So, here is an alternative...

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

这篇关于SQL查询以匹配卖家和买家的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！