How do I fix this SQL query returning improper values?

Question

I am writing an SQL query which will return a list of auctions a certain user is losing, like on eBay.

This is my table:

bid_id  bid_belongs_to_auction  bid_from_user   bid_price
6       7                       1               15.00
8       7                       2               19.00
13      7                       1               25.00

The problematic area is this (taken from my full query, placed at the end of the question):

      AND EXISTS (
              SELECT 1 
                FROM bids x 
               WHERE x.bid_belongs_to_auction = bids.bid_belongs_to_auction
                 AND x.bid_price > bids.bid_price
                 AND x.bid_from_user <> bids.bid_from_user
                 )

The problem is that the query returns all the auctions on which there are higher bids, but ignoring the user's even higher bids.

So, an example when the above query works:

bid_id  bid_belongs_to_auction  bid_from_user   bid_price
6       7                       1               15.00
7       7                       2               18.00

In this case, user 1 is returned as losing the auction, because there is another bid higher than the users bid.

But, here is when the query doesn't work:

bid_id  bid_belongs_to_auction  bid_from_user   bid_price
6       7                       1               15.00
8       7                       2               19.00
13      7                       1               25.00

In this case, user 1 is incorrectly returned as losing the auction, because there is another bid higher than one of his previous bids, but the user has already placed a higher bid over that.

If it's important, here's my full query, but I think it won't be necessary to solve the aforementioned problem, but I'm posting it here anyway:

$query = "
   SELECT
      `bid_belongs_to_auction`,
      `auction_unixtime_expiration`,
      `auction_belongs_to_hotel`,
      `auction_seo_title`,
      `auction_title`,
      `auction_description_1`
   FROM (
      SELECT
         `bid_belongs_to_auction`,
         `bid_from_user`,
         MAX(`bid_price`) AS `bid_price`,
         `auctions`.`auction_enabled`,
         `auctions`.`auction_unixtime_expiration`,
         `auctions`.`auction_belongs_to_hotel`,
         `auctions`.`auction_seo_title`,
         `auctions`.`auction_title`,
         `auctions`.`auction_description_1`
      FROM `bids`
      LEFT JOIN `auctions` ON `auctions`.`auction_id`=`bids`.`bid_belongs_to_auction`
      WHERE `auction_enabled`='1' AND `auction_unixtime_expiration` > '$time' AND `bid_from_user`='$userId'
      AND EXISTS (
              SELECT 1 
                FROM bids x 
               WHERE x.bid_belongs_to_auction = bids.bid_belongs_to_auction
                 AND x.bid_price > bids.bid_price
                 AND x.bid_from_user <> bids.bid_from_user
                 )
      GROUP BY `bid_belongs_to_auction`
   ) AS X
   WHERE `bid_from_user`='$userId'
";

Answer 1

Here's a different approach:

$query = "
   SELECT
      `max_bids`.`bid_belongs_to_auction`,
      `auctions`.`auction_unixtime_expiration`,
      `auctions`.`auction_belongs_to_hotel`,
      `auctions`.`auction_seo_title`,
      `auctions`.`auction_title`,
      `auctions`.`auction_description_1`
   FROM `auctions`
     INNER JOIN (
        SELECT
           `bid_belongs_to_auction`,
           MAX(`bid_price`) AS `auction_max_bid`,
           MAX(CASE `bid_from_user` WHEN '$userId' THEN `bid_price` END) AS `user_max_bid`
        FROM `bids`
        GROUP BY `bid_belongs_to_auction`
     ) AS `max_bids` ON `auctions`.`auction_id` = `max_bids`.`bid_belongs_to_auction`
   WHERE `auctions`.`auction_enabled`='1'
     AND `auctions`.`auction_unixtime_expiration` > '$time'
     AND `max_bids`.`user_max_bid` IS NOT NULL
     AND `max_bids`.`user_max_bid` <> `max_bids`.`auction_max_bid`
";

Basically, when you are retrieving the max bids for all the auctions, you are also retrieving the specific user's max bids along. Next step is to join the obtained list to the auctions table and apply an additional filter on the user's max bid being not equal to the auction's max bid.

Note: the `max_bids`.`user_max_bid` IS NOT NULL condition might be unnecessary. It would definitely be so in SQL Server, because the non-nullness would be implied by the `max_bids`.`user_max_bid` <> `max_bids`.`auction_max_bid` condition. I'm not sure if it's the same in MySQL.