title: Fetching MAX MIN in mysql
body: <p><a href="http://sqlfiddle.com/#!2/5c398/4" rel="nofollow">Original Fiddle</a> </p>
  
  <p><a href="http://sqlfiddle.com/#!2/5c398/12" rel="nofollow">Failed attempt</a></p>
  
  <p><a href="http://sqlfiddle.com/#!2/5c398/15" rel="nofollow">Failed Query</a></p>
  
  <p>I need some help on pulling each brand's most expensive and cheapest products in the same row, the result should be like this:</p>
  
  <pre><code>BRAND_NAME  EX_PRODUCT_ID   EX_NAME EX_PRICE  CHEAP_NAME  CHEAP_PRODUCT_ID  CHEAP_PRICE
  HTC         7               Item G  700       Item A         1                500
  Sony        5               Item B  100       Item F         2                10
  Nokia       4               Item D  260       Item C         3                250
  </code></pre>
  
  <p>But the following query doesn't match the <code>MAX(price)</code>,<code>MIN(price)</code> with the corresponding products:</p>
  
  <pre><code>BRAND_NAME  EX_PRODUCT_ID   EX_NAME EX_PRICE CHEAP_NAME CHEAP_PRODUCT_ID CHEAP_PRICE
  HTC         1               Item A   700     Item A     1                500
  Sony        2               Item B   100     Item B     2                10
  Nokia       3               Item C   260     Item C     3                250
  
  
  SELECT t1.brand_name,t1.ex_product_id,t1.ex_name,
         t1.ex_price,
         t2.cheap_name,t2.cheap_product_id,
         t2.cheap_price        
  FROM(
  
    SELECT m.product_id AS ex_product_id,
         m.name AS ex_name,
         MAX(m.price) AS ex_price,
         b.brand_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
     LEFT JOIN (
         SELECT m.product_id,
             m.name,
             MAX(m.price) AS ex_price
          FROM model m   
     )ex ON ex.ex_price = ex_price  /*** problem here*****/
         AND ex.product_id = m.product_id   
     GROUP BY b.brand_id
  )t1
  
  JOIN (
    SELECT m.product_id AS cheap_product_id,
           m.name AS cheap_name,
           MIN(m.price) AS cheap_price,
           b.brand_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 ch_price
          FROM model m   
  
     )ch  ON ch.ch_price = cheap_price   /*** problem here*****/
          AND ch.product_id = m.product_id 
     GROUP BY b.brand_id
  )t2 ON t2.brand_name = t1.brand_name
  
  
  LIMIT 10
  </code></pre>
  
  <p>Can anyone point me on how to match the maximum and minimum prices for each brand back with the corresponding expensive and cheap products? My query obviously isn't able to identify the <code>MAX</code> and <code>MIN</code> columns in the on clause.</p>