SQL max() with inner joins

Vote count:

0

I am creating an auction website. Here people can look for items and place bets on them. In the user account area I wish to have a list showing all the items where the user has placed a bet on.

Ofcourse each item can have more than one bet from different users so I only wish to show the one with the highest amount of money. This is so that the user can follow all the items on which he placed a bet on, and can track if he is still the one with the highest amount or not.

This is how my database looks like:

Tabel item                      Tabel itembid                          Tabel user
+=========================+     +================================+     +==============+
|id | title | description |     |id | item_id | user_id | amount |     |id | username |
+=========================+     +================================+     +==============+
| 1 | item1 | ........... |1   *| 1 |    1    |    2    |   10   |*   1| 1 |    me    |
| 2 | item2 | ........... |-----| 2 |    1    |    1    |   15   |-----| 2 |  myself  |
| 3 | item3 | ........... |     | 3 |    2    |    3    |   5    |     | 3 |    I     |
+=========================+     | 4 |    2    |    1    |   10   |     +==============+
                                +================================+

So as shown above, I have 3 tables (item, itembid and user). As you can see user 'me' has placed 2 bets, once on item 1 and once on item 2. It turns out that he is also currently the one with the highest bets on both items.

Now user 'myself' placed a bet before on item1 and 'I' placed a bet on item2. However, they are not anymore the ones with the highest bet (user 'me' is). Now I need an SQL statement that gives me a list of information (title, description, amount, username) that is a list of all items where I once placed a bet on. If I am the one currently with the highest bit for that item, I need to see the title and description of the item together with the amount that I placed for my bet as well as my username. Now, if I am not the one with the highest bet, I stil want to see the information of that item but now with the amount and username of the one with the highest bet.

So an example, looking in the perspective of user 'me', I want to see:

> item1, 15, me
> item2, 10, me

Now for user 'myself' I wish to see:

> item1, 15, me

(since 'me' once placed a bet for item1 but is no longer the one with highest amount, user 'me' is)

This is what I have so far but isn't working quiet well...

SELECT i.id, i.user_id, ib.amount, u.username
FROM item i
INNER JOIN itembid ib ON i.id = ib.item_id
INNER JOIN user u ON ib.user_id = u.id
WHERE ib.amount = (SELECT max(amount) FROM itembid ib2 WHERE ib2.id = ib.id)
AND ib.user_oid = 1

asked 32 secs ago

RazorAlliance192

95