title: SQL JOIN with the same table
body: <p>I am trying to query in SQL and I can not resolve it.
  I have a table <strong><code>tCliente</code></strong>:</p>
  
  <p><a href="https://i.stack.imgur.com/gxuqq.png" rel="noreferrer"><img src="https://i.stack.imgur.com/gxuqq.png" alt="enter image description here"></a></p>
  
  <p>What I want to do is a JOIN with the same table to find each pair of clients that lives in the same city.</p>
  
  <p>I try to do this:</p>
  
  <pre><code>SELECT DISTINCT c.codiClien, c.nombreClien, c1.codiClien, c1.nombreClien, c.ciudadClien
  FROM tCliente c
  INNER JOIN tCliente c1 ON c.ciudadClien = c1.ciudadClien
  </code></pre>
  
  <p>and get this:</p>
  
  <p><a href="https://i.stack.imgur.com/UV9mj.png" rel="noreferrer"><img src="https://i.stack.imgur.com/UV9mj.png" alt="enter image description here"></a></p>
  
  <p>But I should get <strong>this:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/aHkWt.png" rel="noreferrer"><img src="https://i.stack.imgur.com/aHkWt.png" alt="enter image description here"></a></p>
  
  <p>I know I have to filter data, but I have tried many things and I can not find the solution.
  Also, I tried to use <strong><code>GROUP BY</code></strong> but it is not possible. I wanted to group by <strong>pair</strong>, that is, something like this:</p>
  
  <pre><code>...
  GROUP BY c.codiClien, c1.codiClien
  </code></pre>
  
  <p>But in doing so I get errors in the query. Could someone please help me? Thanks</p>
  
  <p><strong>Note:</strong>
  When using <strong><code>ON</code></strong> in the <strong><code>INNER JOIN</code></strong>, I would like to know if it is "possible" to do that or should not do it, because the usual thing is to do <code>tb1.id = tb2.id</code></p>