Find row with lowest column value per group, then join distinct group data to another table

Question

I need a hint for this problem, trying for days already to solve it:

THE PROBLEM I am working with an Joomla extension which has this 2 tables of interest:

1. COMPANIES
2. PRODUCTS

A module lists all companies. Now I need to add to each of this displayed companies the cheapest Price of its products, along with some other values connected to this cheapest price (in the same db row).

So I JOIN the MySQL-Query for the Company-Array to the products table and find out the MIN price:

EDIT

$db = $this->getDbo();
$query = $db->getQuery(true);

// QUERY TO DISPLAY THE COMPANIES

SELECT a.id, a.title AS CompanyName, a.compdiscount AS CompanyDiscount,
p.min_price AS MinimalPrice, p.company_id, p.title AS Product, p.price AS
Price, p.discount AS ProductDiscount, p.lowprice AS Lowprice, p.peakprice
AS Peakprice 
FROM `company2` AS a 

//SUBQUERY TO PRODUCTS 
LEFT JOIN (SELECT MIN(price) AS
min_price, company_id, title, price, discount, lowprice, peakprice 
FROM products2 GROUP BY company_id) AS p 
ON p.company_id = a.id 
GROUP BY a.id; 

I have created a Fiddle:

https://www.db-fiddle.com/f/w7fFNS78mCjQCiEvpqdeGd/0

THE (STILL NOT WORKING) RESULT In the fiddle I have created 4 tables: 1.+2. Real-data tables 3.+4. Reduced copies of these tables

And 4 Queries: 1.+3. Just show all data 2. Try to use the subquery with the real-data tables - NOT WORKING (the output is correct in displaying 3 companies and their minnimum price, but the other values are not from the same rows 4. Use the subquery with the test-data - WORKING - displaying 3 companies with their cheapest price and the values of the same rows.

Answer 1

Follow any of the techniques demonstrated on https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html (just be sure to invert the logic to ensure min values instead of max values) to create a subquery to LEFT JOIN on.

There will be dozens of posts on this topic on the sess pool that is the SQL tag on Stack Overflow such as:

• MySQL - Fetching lowest value
• SQL query to select distinct row with minimum value
• how to select one row from several rows with minimum value
• and dozens more that will keep repeating the same basic techniques.

I believe your SQL attempt fundamentally fell down because you were SELECTing MIN with other values (without controlling where within the GROUP's aggregate the data is coming from).

Raw SQL: (DB-Fiddle)

SELECT c.id,
       c.title         AS CompanyName,
       c.contact_email AS CompanyDiscount,
       p.title         AS Product,
       p.price         AS Price,
       p.prodiscount   AS ProductDiscount,
       p.lowprice      AS Lowprice,
       p.peakprice     AS Peakprice 
FROM jos4py_cddir_content AS c
LEFT JOIN (
      SELECT p1.*
      FROM jos4py_cddir_products      AS p1
      LEFT JOIN jos4py_cddir_products AS p2 ON p1.company_id = p2.company_id
                                               AND p1.price > p2.price
      WHERE p2.price IS NULL
) AS p ON c.id = p.company_id

Here is my untested attempt at Joomla syntax with some additional bits and pieces to help with diagnostics. Please do not ever present raw query or error details on your public site.

try {
    $db = JFactory::getDbo();

    $min_subquery = $db->getQuery(true)
                       ->select("p1.*")
                       ->from("#__cddir_products AS p1")
                       ->leftJoin("#__cddir_products AS p2 ON p1.company_id = p2.company_id AND p1.price > p2.price")
                       ->where("p2.price IS NULL");

    $query = $db->getQuery(true)
                ->select("c.id,
                          c.title         AS CompanyName,
                          c.contact_email AS CompanyDiscount,
                          p.title         AS Product,
                          p.price         AS Price,
                          p.prodiscount   AS ProductDiscount,
                          p.lowprice      AS Lowprice,
                          p.peakprice     AS Peakprice")
                ->from("#__cddir_content AS c")
                ->leftJoin("($min_subquery) AS p ON c.id = p.company_id");

    $db->setQuery($query);
    JFactory::getApplication()->enqueueMessage($query->dump(), 'info');
    if (!$result = $db->loadAssocList()) {
        echo "<p>No Rows Found</p>";
    } else {
        echo "<pre>";
        var_export($result);
        echo "</pre>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // don't show $e->getMessage() to public
}

Answer 2

I assume that when you join the companies and products you don't really mean a.id = p.id?

Assuming that the product table has a company_id then you could do a generic subquery approach like the following. Not the most efficient but easy to think. The essential point being, you can't just join to the min of another table, but you can use a subquery to join to the subset of the table that you want.

$db = $this->getDbo();
$query = $db->getQuery(true);

$query->select('a.id, a.title, p.min_price, p.discount, p.morevalues');
$query->from('#__company AS a');

// join to the result of the subquery
$query->join('LEFT', '(SELECT MIN(price) as min_price, company_id, discount, morevalues FROM #__products GROUP BY company_id) AS p', 'p.company_id = a.id');
$query->group('a.id');

$db->setQuery($query, 0, $params->limit);
$items = $db->loadObjectList();
return $items;

Answer 3

WORKING SOLUTION!!

The answer of mickmackusa brought the solution, just needed to adopt some things and get the correct Joomla syntax to make the code running. Posting the running example for anyone who could use it:

$db = JFactory::getDbo();

    $subquery = $db->getQuery(true);
                $subquery->select('p1.*');
                $subquery->from($db->quoteName('#__products') . ' AS p1');
                $subquery->join('LEFT', '#__products AS p2 
                        ON (p1.company_id = p2.company_id AND p1.price > p2.price)');
                $subquery->where('p2.price IS NULL');
   
    $query = $db->getQuery(true);
                
                $query->select('
                        a.id                AS id,
                        a.whatever_x        AS whatever_x,
                        p.title             AS Product,
                        p.price             AS price,
                        p.lowprice          AS lowprice,
                        p.whatever_y        AS whatever_y');
 

    $query->from($db->quoteName('#__content') . ' AS a');
    $query->join('LEFT', '(' . $subquery . ')' . ' AS p ON a.id = p.company_id');
  

    $db->setQuery($query, 0, $params->limit);
    $items = $db->loadObjectList();

        return $items;

Additional info: I did not know about the very helpful trick with throwing out SQL statements posted by mickmackusa. For every beginner like me: Put your code inside of this construction:

// start 
try {

// put here whatever SQL 

// close it with this code
    $db->setQuery($query);
    JFactory::getApplication()->enqueueMessage($query->dump(), 'info');
    if (!$result = $db->loadAssocList()) {
        echo "<p>No Rows Found</p>";
    } else {
        echo "<pre>";
        var_export($result);
        echo "</pre>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  
}

// don't show $e->getMessage() to public!