SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Spain
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select max from another table

    hi all, I have two databases, one with details of a product and another with availability and prices in another table with multiple rows per product.

    I wish to select the details of each from the details table but also the min and max price of each for each product from the other table, i have tried the code below but it gives me an error #1140 about mixing group columns

    SELECT p.product_id, max(a.availability_price), min(a.availability_price)
    FROM products p, availability a
    WHERE a.product_id = p.product_id

    any idea where to go from here?

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    You must add a GROUP BY clause.

    Code:
    SELECT p.product_id, max(a.availability_price), min(a.availability_price)
    FROM products p, availability a
    WHERE a.product_id = p.product_id
    GROUP BY p.product_id
    Yours, Erik.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Spain
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks erik

    It was so simple, yet I didnt know where to start or what to apply it to

    cheers


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
  •