How do I fix this SQL query returning improper values?

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 losi开发者_JAVA百科ng 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'
";

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.

Untested, but this is how I would approach it. Ought to perform OK if there's an index on userid and also one on auctionid.

           select OurUserInfo.auctionid, OurUserInfo.userid, 
           OurUserInfo.ourusersmaxbid, Winningbids.TopPrice
           from

           (

           select A.auctionid, A.userid, max(A.price) as OurUsersMaxBid
           from auctions A where userid = ?
           group by A.auctionid, A.userid

           ) as OurUserInfo


           inner join


           (                      
          -- get the current winning bids for all auctions in which our user is bidding
           select RelevantAuctions.auctionid, max(auctions.price) as TopPrice
            from auctions inner join            
           (
           select distinct auctionid from auctions where userid = ?  -- get our user's auctions
           ) as RelevantAuctions
           on auctions.auctionid = RelevantAuctions.auctionid
           group by RelevantAuctions.auctionid


            ) as WinninBids


            on OurUserInfo.auctionid = winningbids.auctionid

            where WinninBids.TopPrice > OurUserInfo.ourusersmaxbid

Instead of

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

try this:

SELECT 1
  FROM (SELECT BID_ID,
               BID_BELONGS_TO_AUCTION,
               BID_FROM_USER,
               BID_PRICE
          FROM (SELECT BID_ID,
                       BID_BELONGS_TO_AUCTION,
                       BID_FROM_USER,
                       BID_PRICE,
                       RANK ()
                       OVER (
                          PARTITION BY BID_BELONGS_TO_AUCTION, BID_FROM_USER
                          ORDER BY BID_PRICE DESC)
                          MY_RANK
                  FROM BIDS)
         WHERE MY_RANK = 1) 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;