title: LEFT JOIN 2 columns within the same table if the same value is available in both column
body: <p>I have this table. What I want to achieve is a <code>LEFT JOIN</code> to only work if 2 values within the same column is present.</p>
  
  <p>For example, <code>LEFT JOIN</code> will only work on <code>EA-AC-A060047</code> since it's both available for credit and debit columns. And so on.</p>
  
  <p><a href="https://i.stack.imgur.com/nrZbo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nrZbo.png" alt="pic1"></a></p>
  
  <p>This is what I'm trying to achieve.</p>
  
  <p><a href="https://i.stack.imgur.com/PojF5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PojF5.png" alt="pic2"></a></p>
  
  <p>This is what I got so far, no luck.</p>
  
  <pre><code>SELECT 
      t1.referenceno, t2.credit, t3.debit 
  FROM
      (SELECT 
           referenceno, credit, debit, employeeidno 
       FROM
           earningsamendment 
       WHERE 
           employeeidno = 'D0080000010') AS t1
  LEFT JOIN
      (SELECT 
           referenceno, credit, debit, employeeidno 
       FROM
           earningsamendment 
       WHERE
           employeeidno = 'D0080000010') AS t2 ON t2.referenceno = t1.referenceno
  LEFT JOIN
      (SELECT 
           referenceno, credit, debit, employeeidno 
       FROM
           earningsamendment 
       WHERE
           employeeidno = 'D0080000010') AS t3 ON t3.referenceno = t1.referenceno
  WHERE
      t2.referenceno = t1.referenceno 
      AND t3.referenceno = t1.referenceno
  ORDER BY  
      t1.referenceno
  </code></pre>