SQL JOIN with the same table

Question

I am trying to query in SQL and I can not resolve it. I have a table tCliente:

What I want to do is a JOIN with the same table to find each pair of clients that lives in the same city.

I try to do this:

SELECT DISTINCT c.codiClien, c.nombreClien, c1.codiClien, c1.nombreClien, c.ciudadClien
FROM tCliente c
INNER JOIN tCliente c1 ON c.ciudadClien = c1.ciudadClien

and get this:

But I should get this:

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 GROUP BY but it is not possible. I wanted to group by pair, that is, something like this:

...
GROUP BY c.codiClien, c1.codiClien

But in doing so I get errors in the query. Could someone please help me? Thanks

Note: When using ON in the INNER JOIN, I would like to know if it is "possible" to do that or should not do it, because the usual thing is to do tb1.id = tb2.id

Fellmonger

Answer 1

You must exclude itself on the inner join.

SELECT c.codiClien, c.nombreClien, c1.codiClien, c1.nombreClien, c.ciudadClien
FROM tCliente c
INNER JOIN tCliente c1 
    ON c.ciudadClien = c1.ciudadClien
    AND c.codiClien < c1.codiClien;

Unruh

@GordonLinoff due codiClien seems to be index. Yes, clever, Thank you. – Unruh