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

Databases
1

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:

"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?

"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 
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

:slight_smile:

3

thanks again. Works brilliantly.