Prevent double matches on self referencing table

52 views Asked by At

Is there a quick and simple way in SQL to accomplish this, I have drawn up a long way to prevent this, by doing a "nested" self reference. Is there a simpler way?

Issue:

When you are looking for similarities in table column you get [ID1, ID2] and [ID2, ID1] when you really only want one of them.

Example:

SELECT * 
FROM Table t1
INNER JOIN Table t2 ON t1.col = t2.col
WHERE t1.id <> t2.id

Result:

t1.id t2.id t1.col t2.col
1 3 sushi sushi
3 1 sushi sushi
2 4 taco taco
4 2 taco taco

Desired result:

t1.id t2.id t1.col t2.col
1 3 sushi sushi
2 4 taco taco
1

There are 1 answers

5
Salman A On
where t1.id < t2.id

It's that simple.