Geek asks and answers

How to fix this SQL query returning invalid values?

I am writing an SQL query that will return a list of auctions that a specific user will lose , for example, 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 problem area is this (taken from my full request 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 request returns all auctions that have higher bids, but the user ignores 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 returns as an auction loss because there is a different bid higher than the bid for users.

But here, when the request does not 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

1 , , .

, , , , :

$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'
";
+3
3

:

$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`
";

, , . auctions , .

: `max_bids`.`user_max_bid` IS NOT NULL . SQL Server, `max_bids`.`user_max_bid` <> `max_bids`.`auction_max_bid`. , MySQL.

+2

, . , userid, .

           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 auctions
           ) as RelevantAuctions
           on auctions.auctionid = RelevantAuctions.auctionid
           group by RelevantAuctions.auctionid


            ) as WinninBids


            on OurUserInfo.auctionid = winningbids.auctionid

            where WinninBids.TopPrice > OurUserInfo.ourusersmaxbid
+1

Instead

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 the following:

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;
0
source

More articles:


All Articles