 <p>If I understand correctly, you can do this with conditional aggregation. Just find the minimum and maximum prices in a subquery, and then pull the data out for those:</p> <pre><code> SELECT b.brand_name, minmax.price_max, max(case when m.price = minmax.price_max then m.product_id end) as max_product_id, max(case when m.price = minmax.price_max then m.name end) as max_name, minmax.price_min, max(case when m.price = minmax.price_min then m.product_id end) as min_product_id, max(case when m.price = minmax.price_min then m.name end) as min_name FROM model m INNER JOIN series s ON s.series_id = m.series_id INNER JOIN brand b ON b.brand_id = s.brand_id INNER JOIN (SELECT m.product_id, m.name, MIN(m.price) AS price_min, MAX(m.price) AS price_max FROM model m ) minmax ON m.price IN (minmax.price_min, minmax.proc_max) AND minmax.product_id = m.product_id GROUP BY b.brand_id </code></pre> <p>Note if there are multiple products with the same minimum or maximum price, this pulls out an arbitrary one. You can get all of them by using <code>group_concat()</code> instead of <code>max()</code>.</p>