Query problem, get results from two tables.

[jessica_s76]

I have two tables: product and prices.

CREATE TABLE `products` ( 
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`productname` VARCHAR( 255 ) NOT NULL 
) TYPE = MYISAM ;

CREATE TABLE `prices` ( 
`fk_productid` INT UNSIGNED NOT NULL ,
`retailer` VARCHAR( 255 ) NOT NULL ,
`price` INT UNSIGNED NOT NULL 
) TYPE = MYISAM ;

Products sample data
1 Test product
2 Sample product

Prices sample data
1 mystore 1000
1 otherstore 1020
1 thestore 1241
2 astore 451
2 newstore 331
2 mystore 553
2 thestore 112

Now I would like to get all products and the two or three prices with the lowest price for each product. And this preferable with only one query. I don’t want to have another query in a loop at least. Since I will run this in a php-script with a lot of products and a lot of prices.
In my humble mind I thought that this would work but no! It returns two prices for product id 2 and nothing else.

SELECT *
FROM products p1
INNER JOIN ( 
SELECT * 
FROM prices
ORDER BY price ASC 
LIMIT 2 
) p2
ON p1.id = p2.fk_productid 

Result:

id	productname	fk_productid	retailer	price  
2	Sample product	2		thestore	112 
2	Sample product	2		newstore	331

Do I need to create a stored procedure or something?
All help are appreciated!!
Here is the full dbstructure with sample data:

CREATE TABLE `prices` (
  `fk_productid` int(10) unsigned NOT NULL,
  `retailer` varchar(255) NOT NULL,
  `price` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `prices` VALUES (1, 'mystore', 1000);
INSERT INTO `prices` VALUES (1, 'otherstore', 1020);
INSERT INTO `prices` VALUES (1, 'thestore', 1241);
INSERT INTO `prices` VALUES (2, 'astore', 451);
INSERT INTO `prices` VALUES (2, 'newstore', 331);
INSERT INTO `prices` VALUES (2, 'mystore', 553);
INSERT INTO `prices` VALUES (2, 'thestore', 112);

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `productname` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `products` VALUES (1, 'Test product');
INSERT INTO `products` VALUES (2, 'Sample product');

Edit:

Using MySQL 5.0.13, i can upgrade if it would help

[r937]

select prd.productname
     , prc1.price
  from products as prd
inner
  join prices as prc1
    on prd.id = prc1.fk_productid
inner
  join prices as prc2
    on prd.id = prc2.fk_productid
   and prc1.price >= prc2.price
group
    by prd.productname
     , prc1.price
having count(*) <= 2   
order
    by prd.productname
     , prc1.price
     
productname     price
Sample product   112
Sample product   331
Test product    1000
Test product    1020

[jessica_s76]

Amazing!
now you are my

[briansol]

select prd.productname
     , prc1.price
  from products as prd
inner
  join prices as prc1
    on prd.id = prc1.fk_productid
inner
  join prices as prc2
    on prd.id = prc2.fk_productid
   and prc1.price >= prc2.price
group
    by prd.productname
     , prc1.price
having count(*) <= 2   
order
    by prd.productname
     , prc1.price

r937, i know you know your stuff. you've helped me before

But, i don't understand why you would join to the same table twice?
I mean, i follow your code, but to me this seems like not the best way to do this.

[guelphdad]

What do you think is wrong with joining the same table twice? What alternative do you think exists for getting the information you need?

Are you just worried about performance issues or something else?

[briansol]

I guess I've just never seen it done.

[r937]

But, i don't understand why you would join to the same table twice?
I mean, i follow your code, but to me this seems like not the best way to do this.

well, if you follow it, you know why

the self join matches every price for a given product to every other price for the same product, with GROUP BY to count how many prices are lower than than the given price for the given product, and HAVING to keep only those prices which have 2 or fewer lower prices

i.e. it returns the 2 lowest prices per product

that may not seem like the best way to do it, but it works