Monica Heddneck Monica Heddneck - 4 months ago 9
SQL Question

How to avoid self-joins that result in symmetric results in MySQL?

I was looking for records that are within 2 weeks of each other, in the same table, as such:

SELECT stuff
FROM mytable AS a
JOIN mytable AS b
ON a.ID = b.ID
WHERE
(
a.Date = b.Date
OR
a.Date BETWEEN DATE_SUB(b.Date, INTERVAL 14 DAY) AND DATE_ADD(b.Date, INTERVAL 14 DAY)
OR
b.Date BETWEEN DATE_SUB(a.Date, INTERVAL 14 DAY) AND DATE_ADD(a.Date, INTERVAL 14 DAY)
)
;


It worked fine, but now I have a result with this type of structure:

| ID | a.Date | b.Date | a.Value | b.Value |
|----|------------|------------|---------|---------|
| 1 | 2016-01-01 | 2016-01-02 | foo | bar |
| 1 | 2016-01-02 | 2016-01-01 | bar | foo |


Either I did my join in a bad way which is leading to this duplicated structure, or the join is okay but I need some way to remove the chiral record. Can anyone advise me on how to proceed?

Answer

Add:

a.Value < b.Value

to the WHERE clause.

Or, better yet, if you have a primary key (and all tables should have a primary key):

a.pk < b.pk