title: How to correct column name values in MySQL multiple JOINS query
body: <p>I need to display a product with its price, and current stock (if any) of that product with a specific color and attributes. It does what it should, partially. This is the explanation of the query (or at least what it should be doing):</p>
  
  <ul>
  <li>Get basic information about the product from <code>store_product</code></li>
  <li>A product has a stock. That stock is connected to a color ID, and an attribute detail ID.</li>
  <li>A product stock that's associated with a color ID can have multiple attribute detail IDs. So for example, a red shirt stock value from poliester material can be different than a red shirt stock value from M size.</li>
  </ul>
  
  <p>For this particular example, i'm trying to receive the stock value for product ID <code>59</code> that meet these specific values</p>
  
  <ul>
  <li><code>id_color</code> must be <code>1</code> (1 = <em>red</em>, which has a custom name called <em>red deadpool</em>)</li>
  <li><code>id_detail</code> must be <code>4</code> in one row, and <code>50</code> in another row (<code>4</code> = <em>Size M</em> / <code>50</code> = <em>Material Poliester</em>)</li>
  </ul>
  
  <p>This is what i'm currently receiving:</p>
  
  <p><a href="https://i.stack.imgur.com/8soRy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8soRy.png" alt="current result"></a></p>
  
  <p>The query is partially correct because:</p>
  
  <ul>
  <li>I'm getting 2 rows, one for each color/attribute combination</li>
  <li>I'm getting the correct <code>id_stock</code> ID for each row, and i'm also getting the correct stock value for the color/attribute combination.</li>
  </ul>
  
  <p>The issue is that, although the stock information is correct, the <code>id_attribute</code> / <code>attribute</code> / <code>id_detail</code> / <code>detail</code> information is wrong.</p>
  
  <p>This is what i would <strong>need</strong> to receive (i've marked the correction in bold):</p>
  
  <p><a href="https://i.stack.imgur.com/IBdqc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IBdqc.png" alt="corrected result"></a></p>
  
  <pre><code>SELECT store_product.id_product             AS id_product, 
         store_product.NAME                   AS NAME, 
         store_product.price                  AS price, 
         store_product.flg_stock              AS flg_stock, 
         store_product_stock.id               AS id_stock, 
         store_product_stock.stock            AS stock, 
         Ifnull(store_color.NAME, color.NAME) AS color, 
         color.hex                            AS hex, 
         store_attribute.id_attribute         AS id_attribute, 
         store_attribute.NAME                 AS attribute, 
         store_attribute_detail.id_detail     AS id_detail, 
         store_attribute_detail.NAME          AS detail 
  FROM   store_product 
         LEFT JOIN store_product_color 
                ON store_product_color.id_color = store_product_color.id_color 
         LEFT JOIN color 
                ON color.id_color = store_product_color.id_color 
         LEFT JOIN store_color 
                ON store_color.id_color = color.id_color 
                   AND store_color.id_store = 1 
         LEFT JOIN store_product_detail 
                ON store_product_detail.id_product = store_product.id_product 
         LEFT JOIN store_attribute_detail 
                ON store_attribute_detail.id_detail = 
                   store_product_detail.id_detail 
         LEFT JOIN store_attribute 
                ON store_attribute.id_attribute = 
                   store_attribute_detail.id_attribute 
         LEFT JOIN store_product_stock 
                ON store_product_stock.id_product = store_product.id_product 
                   AND store_product_stock.id_color = 1 
                   AND store_product_stock.id_detail IN( 4, 50 ) 
  WHERE  store_product.id_store = 1 
         AND store_product.id_product = 59 
         AND store_product_color.id_color = 1 
  GROUP  BY store_product_stock.id 
  </code></pre>
  
  <p>Here you can find the whole table structure and the current query which has the issue: <a href="http://sqlfiddle.com/#!9/ed4025/1" rel="nofollow noreferrer">sqlfiddle</a></p>
  
  <p>Any ideas on how to solve the problem? Thanks!</p>