Ignore duplicates while using join I'm trying to ignore duplicates between two providers in my tables while using join.

I'm using this query for now:

SELECT *
FROM A AS a1, A AS a2
WHERE a1.provider &lt;&gt; a2.provider AND a1.etype = a2.etype


Unfortunately, that query gives me duplicates (two rows of the same providers, in opposite sides).

I'm trying to ignore rows that appear twice in the opposite sides, for example:

etype    provider    etype    provider
hello    aaa         hello    bbb
hello    bbb         hello    aaa


This dbfiddle illustrates the desired output as well.
What Kondybas explained about the appropriate operator, but with legal syntax:

SELECT etype, a1.provider AS provider1, a2.provider AS provider2
FROM   A a1
JOIN   A a2 USING (etype)
WHERE  a1.provider &lt; a2.provider;


[INNER] JOIN requires a join condition, with either ON or USING.

To keep events with only a single provider in the result, use a LEFT [OUTER] JOIN:

SELECT a1.etype, a1.provider AS provider1, a2.provider AS provider2
FROM   A a1
LEFT   JOIN A a2 ON a1.etype = a2.etype
                AND a1.provider &lt; a2.provider;


The second condition has to move to the join condition as well in this case. See:


Sort by number of related rows in referencing table