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:
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:
$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:
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.