Beginner multi-join question

There are 3 tables for a small trading system; USERS, ITEMS and TRADES, the trades themselves are held in table TRADES (logically enough), as:

column     type
========== =====
seller_id  int
buyer_id   int
item_id    int
quantity   int
cost       int
currency   int

I want to display pending trades, using a 3-way join to map IDs to names in tables USER and ITEM. So far I've got:

SELECT
    users.name AS seller,
    items.id AS item_id,
    items.name AS item,
    trades.item_id AS trade_id,
    trades.quantity AS quantity,
    trades.cost AS cost,
    trades.currency AS currency 
FROM
(trades INNER JOIN users ON trades.seller_id = users.id) 
INNER JOIN items ON trades.item_id = items.id 
WHERE trades.buyer_id = xxx

The first join works fine, but as soon as I pass the result to the second it fails; returns zero data. I'm braced for it being a real noobie problem (in fact I'm hoping it is, easy to fix = good) but I can't see it (probably because I am in 开发者_如何学Cfact a noob at these things).

SELECT 
      users.name AS seller, 
      items.id AS item_id, 
      items.name AS item, 
      trades.item_id AS trade_id, 
      trades.quantity AS quantity, 
      trades.cost AS cost, 
      trades.currency AS currency   
FROM 
      trades
LEFT JOIN 
      users 
ON 
      trades.seller_id = users.id
LEFT JOIN 
      items 
ON 
      trades.item_id = items.id
WHERE trades.buyer_id = xxx 

I think trades_id needs to be trades.item_id

I also advise you to use LEFT JOIN instead of INNER JOIN, only use INNER JOIN when you want to trigger a error if there's no match. When you use LEFT JOIN it always returns data, also when there's no match in the second table. See: http://www.w3schools.com/Sql/sql_join.asp