title: SQL - joining rows from same table
body: <p>I only know the bare min when it comes to SQL so please treat me as a complete noob!</p>
  
  <p>I have a table which has many rows but some of them can pair up by the id and I want return array of those rows merged.</p>
  
  <pre><code> post_id  # meta_name # meta_value  #     
  ======================================
     1      #   bar     #    44       #
     1      #   foo     #    on       #
     2      #   bar     #    1        #
     2      #   foo     #    on       #
     3      #   bar     #    1        #
     3      #   foo     #    off      #
  </code></pre>
  
  <p>as a scaled down version, imagine the above of my table and I am aiming to return 2 results of</p>
  
  <pre><code>   1    #   foo    #  bar    #   44   #  on
     2    #   foo    #  bar    #   1    #  on
  </code></pre>
  
  <p>based on that the ids are related and that c2 has the value of 'on'</p>
  
  <p>my attempt was the following from some reading but not getting anywhere</p>
  
  <pre><code>SELECT 
      t1.post_id, t2.post_id, t1.meta_name, t2.meta_name, t1.meta_value, t2.meta_value 
  FROM 
      `ecom_page_meta` t1 
  JOIN 
      `ecom_page_meta` t2 
  ON 
      t1.post_id = t2.post_id 
  WHERE 
      t1.meta_name = 'featured-products' 
  AND 
      t1.meta_value = 'on'
  </code></pre>
  
  <p>any help would be muchly appreciated</p>
  
  <p>** EDIT ** </p>
  
  <p>I thought Id just post the syntax which worked nicely below thanks to the answer below:</p>
  
  <pre><code>SELECT L.post_id, L.meta_name, R.meta_name, L.meta_value, R.meta_value
  FROM `ecom_page_meta` L
  INNER JOIN ecom_page_meta R
      ON L.post_id = R.post_id
      AND L.meta_name = 'featured-products-order' 
      AND R.meta_value = 'on' 
  WHERE R.meta_name = 'featured-products' 
  ORDER BY L.meta_value 
  DESC
  </code></pre>
  
  <p>THanks again.</p>