title: Joins with two tables conditionally based on the value of ONE column
body: <p>I have following tables to manage purchase and issues of items. </p>
  
  <p>item Table</p>
  
  <pre><code>+---------+-----------+
  | item_id | item_name |
  +---------+-----------+
  |     500 | A4        |
  |     501 | A5        |
  |     502 | B5        |
  |     503 | B4        |
  |     504 | A3        |
  +---------+-----------+
  </code></pre>
  
  <p>supplier Table</p>
  
  <pre><code>+-------------+---------------+
  | sup_id      | supplier_name |
  +-------------+---------------+
  |           1 | ABC Suppliers |
  |           2 | DEF Suppliers |
  |           3 | GHI Suppliers |
  +-------------+---------------+
  </code></pre>
  
  <p>officer Table</p>
  
  <pre><code>+------------+--------------+
  | officer_id | officer_name |
  +------------+--------------+
  |          1 | Jhon         |
  |          2 | William      |
  |          3 | Ken          |
  |          4 | Robert       |
  +------------+--------------+
  </code></pre>
  
  <p>purchase / issue table</p>
  
  <pre><code>+-----------+---------+---------+---------+------------+-----+-------------+
  | update_id | bill_no | sup_id  | item_id |    date    | qty |    type     |
  +-----------+---------+---------+---------+------------+-----+-------------+
  |      1000 |      10 |       1 |     500 | 2018-11-01 |  50 | purchase    |
  |      1001 |      40 |       1 |     500 | 2018-11-02 |  25 | purchase    |
  |      1002 |      32 |       2 |     500 | 2018-11-04 |  10 | issue       |
  |      1003 |      25 |       3 |     500 | 2018-11-05 |  12 | issue       |
  |      1004 |      14 |       1 |     500 | 2018-11-08 |  22 | purchase    |
  |      1005 |      55 |       2 |     501 | 2018-11-09 |  10 | purchase    |
  |      1006 |      30 |       2 |     502 | 2018-11-10 |  40 | purchase    |
  +-----------+---------+---------+---------+------------+-----+-------------+
  </code></pre>
  
  <p>02) purchase / issue table holds the purchase details and issue details by mentioning the "type" at the end of table.</p>
  
  <p>03) I need to get the following output.</p>
  
  <pre><code>+-----------+------------------------------+------------+-----+------------+
  | item_name | supplier_name / officer_name |    date    | qty |type        |
  +-----------+------------------------------+------------+-----+------------+
  | A4        | ABC Suppliers                | 2018-11-01 |  50 | purchase   |
  | A4        | ABC Suppliers                | 2018-11-02 |  25 | purchase   |
  | A4        | William                      | 2018-11-04 |  10 | issue      |
  | A4        | Ken                          | 2018-11-05 |  12 | issue      |
  | A4        | ABC Suppliers                | 2018-11-08 |  22 | purchase   |
  | A5        | DEF Suppliers                | 2018-11-09 |  10 | purchase   |
  | B5        | DEF Suppliers                | 2018-11-10 |  40 | purchase   |
  +-----------+------------------------------+------------+-----+------------+
  </code></pre>
  
  <p>03) I used the following query</p>
  
  <pre><code>select item.item_name, 
         supplier.supplier_name, 
         purchase.date, 
         purchase.qty, 
         purchase.type 
  from purchase 
  join item on item.item_id = purchase.item_id
  where supplier.sup_id in (select sup_id from supplier)
  </code></pre>
  
  <p>04) But I did't get the desired output. I can not understand what I am going wrong. Can anyone help me ?</p>