title: Join two tables, merge and transpose the result
body: <p>Ok, say i have two tables <code>products</code> and <code>product_options</code>.</p>
  
  <p>structure table <code>products</code> be like:</p>
  
  <pre><code>   +--------------------------------------+
     | p_id | product_name | product_status |
     +--------------------------------------+
     |    1 | Keyboard ABC | PENDING        |
     |    2 | Mouse ABC    | ORDERED        |
     |    3 | Monitor ABC  | ORDERED        |
     +--------------------------------------+
  </code></pre>
  
  <p>And table <code>product_options</code> be like:</p>
  
  <pre><code>   +--------------------------------------+
     | po_id | p_id | opt_name | opt_value  +
     +--------------------------------------+
     |    1  |    1 | part_no  | KB-ABC-001 |
     |    2  |    1 | stock    |          0 |
     |    3  |    2 | part_no  | MS-ABC-001 |
     |    4  |    2 | stock    |         14 |
     |    5  |    3 | part_no  | MO-ABC-001 |
     |    6  |    3 | stock    |         10 |
     +--------------------------------------+
  </code></pre>
  
  <p>The question is, I want to display both tables query and has specific criteria for product that I ordered only. And the result be like:</p>
  
  <pre><code>   +------------------------------------------------------------+
     | p_id | product_name | part_no    | stock  | product_status |
     +------------------------------------------------------------+
     |    2 | Mouse ABC    | MS-ABC-001 |     14 | ORDERED        |
     |    3 | Monitor ABC  | MO-ABC-001 |     10 | ORDERED        |
     +------------------------------------------------------------+
  </code></pre>
  
  <p>My SQL Query is:</p>
  
  <pre><code>   SELECT 
            t.p_id, 
            t.product_name,
            (CASE WHEN t.key_name = 'part_no' THEN t.key_value ELSE NULL END) AS part_no,
            (CASE WHEN t.key_name = 'stock' THEN t.key_value ELSE NULL END) AS stock,
            t.product_status
     FROM
            (
                 SELECT 
                        products.p_id as p_id,
                        products.product_name as product_name,
                        products.product_status as product_status,
                        product_options.opt_name as key_name, 
                        product_options.opt_value as key_value
                 FROM 
                        product_options Inner Join 
                        products On product_options.p_id = products.p_id
                 WHERE
                        (products.product_status = 'ORDERED') AND 
                        (product_options.opt_name = 'part_no' OR product_options.opt_name = 'stock')
                ) as t
     GROUP BY t.p_id
  </code></pre>
  
  <p>What I got is:</p>
  
  <pre><code>   +------------------------------------------------------------+
     | p_id | product_name | part_no    | stock  | product_status |
     +------------------------------------------------------------+
     |    2 | Mouse ABC    | MS-ABC-001 |   NULL | ORDERED        |
     |    3 | Monitor ABC  | MO-ABC-001 |   NULL | ORDERED        |
     +------------------------------------------------------------+
  </code></pre>
  
  <p>But, what I want is to display NULL at <code>stock</code> field as from data on <code>product_options</code> has:</p>
  
  <pre><code>   +------------------------------------------------------------+
     | p_id | product_name | part_no    | stock  | product_status |
     +------------------------------------------------------------+
     |    2 | Mouse ABC    | MS-ABC-001 |     14 | ORDERED        |
     |    3 | Monitor ABC  | MO-ABC-001 |     10 | ORDERED        |
     +------------------------------------------------------------+
  </code></pre>
  
  <p>The question is, how can I display / merge two tables with case like this?
  I tried to use <code>GROUP_CONCAT</code>, but that is not what I am searching for. </p>
  
  <p>See <a href="http://sqlfiddle.com/#!9/9dce6/3" rel="nofollow">SQLFiddle</a></p>
  
  <p>Please help.</p>