SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    joining two tables and sorting the values of the 2nd table

    Hi,

    I hope someone can help. I have a table containing a list of products. Each product can have any number of individually priced variants (i.e. Small, Medium, Large, etc) and all these variants are stored in another table.

    The tables look (something) like:

    tblproducts (products table)
    • prodId (unique auto incremental ID)
    • prodTitle (varchar 100)
    • prodDesc (text)


    tblvariants (variants table)
    • variId (unique auto incremental ID)
    • variProdId (INT - this is the field that relates to the prodId)
    • variPrice (decimal 8,2)


    I want to create a SELECT statement that lists all my products (I can do this bit) but I also want the recordset to include the "lowest priced" variant for each product.

    So the result I want is:

    ID = 1
    TITLE = Product 1
    DESCRIPTION: The product description
    LOWEST VARIANT PRICE (for this product): 9.99


    ID = 2
    TITLE = Product 2
    DESCRIPTION: The product description
    LOWEST VARIANT PRICE (for this product): 4.99


    ID = 3
    TITLE = Product 3
    DESCRIPTION: The product description
    LOWEST VARIANT PRICE (for this product): 15.99

    My basic (products only) select statement looks like:
    Code:
    "SELECT prodId, prodTitle, prodDesc FROM xxxdbsql.tblproducts WHERE prodEnabled = 'Y' ORDER BY prodTitle ASC"
    how do I incorporate the variants into this statement to get the lowest priced variant for each product - something like this but making sure the variant is the lowest priced variant?
    Code:
    "SELECT prodId, prodTitle, prodDesc, variPrice FROM xxxdbsql.tblproducts, xxxdbsql.tblvariants WHERE prodEnabled = 'Y' AND prodId = variProdId ORDER BY prodTitle ASC"
    Hope that all makes sense and that someone can help.

    Many thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT p.prodId
         , p.prodTitle
         , p.prodDesc 
         , MIN(v.variPrice) AS lowest_price
      FROM xxxdbsql.tblproducts AS p
    INNER
      JOIN xxxdbsql.tblvariants AS v
        ON v.variProdId = p.prodId
     WHERE p.prodEnabled = 'Y' 
    GROUP
        BY p.prodId
    ORDER 
        BY p.prodTitle ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks again. Works brilliantly.


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
  •