Page 1 of 1

Query problem

Posted: Sun Mar 22, 2009 3:58 am
by itsmani1
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:

Code: Select all

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 ;

Re: Query problem

Posted: Sun Mar 22, 2009 4:28 am
by VladSun
[sql]SELECT     DISTINCT bids.FK_PRODUCT_IDFROM    bidsWHERE    FK_USER_ID = ?    AND    bid_price < (        SELECT             max(lower_bids.bid_price)        FROM            bids AS lower_bids        WHERE            bids.FK_PRODUCT_ID = lower_bids.FK_PRODUCT_ID    )[/sql]

Re: Query problem

Posted: Tue Mar 24, 2009 1:43 am
by itsmani1
I tried this but there is problem,

since one user can place more than one bid in that case it don't gives correct result.

Like user id 2 placed 3 bids of 500,520 and 580 [if we assume that 580 is the max bid by any user it will consider 520 as smaller than max and show this result]

Re: Query problem

Posted: Tue Mar 24, 2009 3:43 am
by VladSun
Well, I think it's too easy to be solved, so it will be your homework ;)

Re: Query problem

Posted: Tue Mar 24, 2009 5:29 am
by itsmani1
I tried this query but can't say wots wrong with it

Code: Select all

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