SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query problem, get results from two tables.

    I have two tables: product and prices.
    Code:
    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.

    PHP Code:
    SELECT *
    FROM products p1
    INNER JOIN 

    SELECT 
    FROM prices
    ORDER BY price ASC 
    LIMIT 2 
    p2
    ON p1
    .id p2.fk_productid 
    Result:
    Code:
    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:
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing!
    now you are my

  4. #4
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    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.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess I've just never seen it done.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by briansol
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •