Oracle SQL Select: getting duplicate results joining tables

I’m starting with SQL and doing some exercises and I’m completely stuck on the last one. It is about looking for streets in the same country name. Two tables are used, locations and countries from the HR schema. The problem is that I don’t know how to avoid duplicate results. For example if I have the street “x” in Canada and the street “y” also in Canada, it shows me twice:

1. street x / Canada / street y
2. street y / Canada / street x and I can’t find a way to correct this.

My select is:

SELECT DISTINCT A.STREET_ADDRESS AS "CALLE A", C.COUNTRY_NAME, B.STREET_ADDRESS AS "CALLE B"
FROM HR.LOCATIONS A JOIN HR.LOCATIONS B ON (A.STREET_ADDRESS <> B.STREET_ADDRESS), HR.COUNTRIES C
WHERE A.COUNTRY_ID = B.COUNTRY_ID AND B.COUNTRY_ID = C.COUNTRY_ID
ORDER BY C.COUNTRY_NAME

I get this Result_

Any ideas? Thank you.

Answer

use < instead of <>

SELECT DISTINCT A.STREET_ADDRESS AS "CALLE A", C.COUNTRY_NAME, B.STREET_ADDRESS AS "CALLE B"
FROM HR.LOCATIONS A JOIN HR.LOCATIONS B ON (A.STREET_ADDRESS > B.STREET_ADDRESS), HR.COUNTRIES C
WHERE A.COUNTRY_ID = B.COUNTRY_ID AND B.COUNTRY_ID = C.COUNTRY_ID
ORDER BY C.COUNTRY_NAME