Query problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Query problem

Post 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 ;
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Query problem

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Re: Query problem

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Query problem

Post by VladSun »

Well, I think it's too easy to be solved, so it will be your homework ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Re: Query problem

Post 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
Post Reply