Query problem

Discussion in 'Databases' started by dizyn, Mar 22, 2009.

  1. #1
    Hi

    I am having problem is selecting non high bids. For example user id 5 made 2 bigs on a product id 20 and both are smaller than the highest bid that is posted by a user id 22. Now I want to select that product for whom user id 5 did not made highest bid.
    Thanks

    Here is my db table structure:
    CREATE TABLE `bids` (
      `PK_ID` int(11) NOT NULL auto_increment,
      `FK_USER_ID` int(11) default NULL,
      `FK_PRODUCT_ID` int(11) default NULL,
      `bid_price` float(11,0) default NULL,
      `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      PRIMARY KEY  (`PK_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;
    Code (markup):
     
    dizyn, Mar 22, 2009 IP
  2. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #2
    If I understood right:

    
    SELECT * FROM bids WHERE bid_price NOT IN(SELECT MAX(bid_price) FROM bids GROUP BY FK_USER_ID,FK_PRODUCT_ID);
    
    Code (markup):
    Regards.
    Edit:
    Actually the above query will return and user id 20 if it has 2bids (max and any else).
    This can be fixed the same way checking userid not in sub-query.
    But, this is not my point. In my opinion your db model is not 100% clear (in particular table bids).Why user can bid more than once on the same product?
     
    koko5, Mar 22, 2009 IP
  3. dizyn

    dizyn Active Member

    Messages:
    251
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #3
    Tried this but can't says wots the problem with it.
    SELECT
        DISTINCT bids.FK_PRODUCT_ID,max(bid_price)
    FROM
        bids
    WHERE
        FK_USER_ID = 2
        AND
        max(bid_price) < (
            SELECT
                max(lower_bids.bid_price)
            FROM
                bids AS lower_bids
            WHERE
                bids.FK_PRODUCT_ID = lower_bids.FK_PRODUCT_ID
        )
        group by bids.FK_PRODUCT_ID
    Code (markup):
     
    dizyn, Mar 24, 2009 IP
  4. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #4
    
    SELECT bids.FK_PRODUCT_ID,max(bid_price)
    FROM
        bids
    WHERE
        FK_USER_ID = 2
    group by bids.FK_PRODUCT_ID
    HAVING
    max(bid_price) < (
            SELECT
                max(lower_bids.bid_price)
            FROM
                bids AS lower_bids
            WHERE
                bids.FK_PRODUCT_ID = lower_bids.FK_PRODUCT_ID
    )
    
    Code (markup):
    Try this
    Not tested, must works.
    Edit: Tested and works fine :)
     
    koko5, Mar 24, 2009 IP