SQL query to match sellers and buyers

Question

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.

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.

MatBailie · Accepted Answer · 2012-02-02T17:02:25.080

1

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

edited Feb 02 '12 at 17:02

answered Jan 31 '12 at 14:52

MatBailie

83,401
18
103
137

Thanks! But it seems this returns all people that are giving away an item the user wants. Not only those that also want an item the user is giving away.. – LarsJK Feb 02 '12 at 13:00
Can you add to your answer why that sintax should be avoided? In my work we think that is so much better reading and we know that this don't change the performance (at least in Oracle). Thanks. – Feb 02 '12 at 13:50
@SérgioMichels - That's a long conversation, and I'm even sure there are questions about it here on SO. My 'short' (ish) answer is... (Technical) When implementing multiple inner/outer joins, the outer join notation causes it to be ambiguous. (Presentation) It's often much easier to understand complex ANSI-92 that it is with older notation. And that makes it easier to maintain and debug. `,` notation *is* often more compact, especially for simple queries. But it's worst-case scenarios are often much worse than ANSI-92's worst case scenarios. – MatBailie Feb 02 '12 at 16:55
@Larsaronen - That's because it read as just a one way transaction, not a swap, sorry ;) I've updated the answer to circle back again and only show rows where seller has what buyer wants, AND buyer has what seller wants. – MatBailie Feb 02 '12 at 17:03
@Dems Tanks for your answer. I've checked and indeed are [answers](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) about this on SO. – Feb 02 '12 at 17:11
Works great! Great readable code as well! (small typo: userwish should be userWants..) – LarsJK Feb 03 '12 at 21:25

score 0 · Answer 2 · 2012-02-02T13:48:52.037

0

I think that what you need is something like this:

select p.*
     , w.item_id  item_wanted
     , g.person_id  person_giveaway
  from People p
     , Wishlist w
     , Giveaways g
 where p.person_id = ?
   and p.person_id = w.person_id
   and g.person_id != p.person_id
   and g.item_id = w.item_id
   and exists( select 1
                 from Wishlist w1
                    , Giveaways g1
                where g1.person_id = p.person_id
                  and g1.item_id   = w1.item_id
                  and w1.person_id = g.person_id )

edited Feb 02 '12 at 13:48

answered Jan 31 '12 at 14:42

Thanks! But it seems this returns all people that are giving away an item the user wants. Not only those that also want an item the user is giving away.. – LarsJK Feb 02 '12 at 13:00
-1: It's really considered best practice to use JOIN statements and avoid using sub-queries, if they're unnecessary. Please try fostering better coding practices (see Dems' post). – Nick Vaccaro Feb 02 '12 at 16:29
@Norla - Although I strongly agree that ANSI-92 is much preferable, I don't think it's a relevant enough reason for a down-vote. *(Just my opinion though)* – MatBailie Feb 02 '12 at 17:11

score 0 · Answer 3 · answered Feb 02 '12 at 16:12

select
      AllGiveWish.WhichWay,
      AllGiveWish.Item_ID,
      p1.Name as MainPersonName,
      p2.Name as OtherPersonName
   from
      ( select   
              "Give" as WhichWay,
              G.Item_ID,
              G.Person_ID as MainPerson,
              W.Person_ID as OtherPerson
           from
              GiveAways G
                 JOIN WishList W
                    on G.Item_ID = W.Item_ID
           where
              G.Person_ID = YourSinglePersonParm 
        UNION ALL
        select   
              "Wish" as WhichWay,
              W.Item_ID,
              W.Person_ID as MainPerson,
              G.Person_ID as OtherPerson
           from
              WishList W
                 JOIN GiveAways G
                    on W.Item_ID = G.Item_ID
           where
              W.Person_ID = YourSinglePersonParm ) As AllGiveWish

      join People P1
            on AllGiveWish.MainPerson = P1.Person_ID

      join People P2
            on AllGiveWish.OtherPerson = P2.Person_ID

SQL query to match sellers and buyers

3 Answers3

Linked