9

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

enter image description here

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:

enter image description here

But I should get this:

enter image description here

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

Venkat
  • 2,549
  • 2
  • 28
  • 61
Jonatan Lavado
  • 954
  • 2
  • 15
  • 26

1 Answers1

20

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;
McNets
  • 10,352
  • 3
  • 32
  • 61