MySQL Query Help

[jack.mitchell]

Ok, so i'm getting myself muddled with joins, max, distinct and unique, you name it i've tried to get my head round it. Basically i'm trying to get a users max bids on a list of items using the following tables:

mysql> select * from bids;
+-------+-----------+----------+-----------+------------+
| bidID | listingID | memberID | bidAmount | bidDate    |
+-------+-----------+----------+-----------+------------+
|     1 |         5 |        2 |      8.00 | 1300902139 |
|     2 |         5 |        3 |     10.00 | 1300903458 |
|     3 |         5 |        2 |     11.00 | 1300904226 |
|     5 |         5 |        3 |     12.00 | 1300905712 |
+-------+-----------+----------+-----------+------------+
5 rows in set (0.00 sec)

mysql> select * from runningListings;
+------------------+----------+-----------------------+------------------------------+----------------+-----------------+--------------+-----------------+------------+------------+--------------+---------------+----------------+
| runningListingID | memberID | listingTitle          | listingDescription           | listingPostage | listingQuantity | listingPrice | listingDuration | startDate  | endDate    | currentPrice | reserverPrice | listingRunning |
+------------------+----------+-----------------------+------------------------------+----------------+-----------------+--------------+-----------------+------------+------------+--------------+---------------+----------------+
|                5 |        2 | Ghost Mantis Subadult | Subadult Female Ghost Mantis | 1.95           |               1 |         NULL |            NULL | 1300881051 | 1301485851 |         6.95 |          NULL |              1 |
|                6 |        2 | Congo Green Adult     | Adult Congo Green Mantis.    | 2.50           |               1 |         NULL |            NULL | 1300887508 | 1302097108 |        11.99 |          NULL |              1 |
+------------------+----------+-----------------------+------------------------------+----------------+-----------------+--------------+-----------------+------------+------------+--------------+---------------+----------------+
2 rows in set (0.00 sec)

mysql>

So far I have managed:

    $query = "SELECT runningListings.listingTitle,
                     runningListings.listingRunning,
                     runningListings.endDate,
                     bids.bidAmount
              FROM runningListings,
                   bids
              WHERE bids.memberID = ".$member->getID();

Which gives me:

mysql> SELECT runningListings.listingTitle, runningListings.listingRunning, runningListings.endDate, bids.bidAmount FROM runningListings, bids WHERE bids.memberID = 2;
+-----------------------+----------------+------------+-----------+
| listingTitle          | listingRunning | endDate    | bidAmount |
+-----------------------+----------------+------------+-----------+
| Ghost Mantis Subadult |              1 | 1301485851 |      8.00 |
| Congo Green Adult     |              1 | 1302097108 |      8.00 |
| Ghost Mantis Subadult |              1 | 1301485851 |     11.00 |
| Congo Green Adult     |              1 | 1302097108 |     11.00 |
+-----------------------+----------------+------------+-----------+
4 rows in set (0.00 sec)

What I should be getting is:

| Ghost Mantis Subadult |              1 | 1301485851 |     11.00 |

Could anyone suggest what I might use to achieve this, i've tried so many different things that i've got myself a bit muddled and don't know right from wrong anymore!

TIA, Jack.

 

[jack.mitchell]

Hi guys,

Thanks I used Simons implementation and it works perfectly.

Does anyone have a link to a good simple tutorial on mysql joins, I really need to brush on my database skills.

Also, just a quick note that memberID in the listings table is indeed who created the listing.

Thanks!