CODEWITHSUNDEEP
mysql
user2149346
user2149346

Reputation: 295

MySQL grouping multiple columns issue

i have a database table containing events:

INSERT INTO events(device, link, down_time, up_time) VALUES('d1', 'l1', '2015-01-01 00:00:00', '2015-01-01 00:05:00');
INSERT INTO events(device, link, down_time, up_time) VALUES('d2', 'l2', '2015-01-01 00:00:01', '2015-01-01 00:00:2');
INSERT INTO events(device, link, down_time, up_time) VALUES('d2', 'l2', '2015-01-01 00:00:03', '2015-01-01 00:00:05');
INSERT INTO events(device, link, down_time, up_time) VALUES('d3', 'l3', '2015-01-01 00:00:06', '2015-01-01 00:00:09');

| device | link | down_time           | up_time             | id |
| d1     | l1   | 2015-01-01 00:00:00 | 2015-01-01 00:05:00 |  1 |
| d2     | l2   | 2015-01-01 00:00:01 | 2015-01-01 00:00:02 |  2 |
| d2     | l2   | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 |  3 |
| d3     | l3   | 2015-01-01 00:00:06 | 2015-01-01 00:00:09 |  4 |

i want to find events, overlapping in time and am using the following query:

SELECT *
FROM events a
JOIN events b
ON a.down_time <= b.up_time AND a.up_time >= b.down_time
WHERE a.link regexp 'l[12]' AND b.link regexp 'l[12]' and a.id != b.id;

This displays 4 rows showing overlaps, however rows 1 and 2 show the same overlap events as rows 3 and 4. I want add a group clause to query so that i only display rows 1 and 2 but cannot get it worked out.

| device | link | down_time           | up_time             | id | device | link | down_time           | up_time             | id |
| d2     | l2   | 2015-01-01 00:00:01 | 2015-01-01 00:00:02 |  2 | d1     | l1   | 2015-01-01 00:00:00 | 2015-01-01 00:05:00 |  1 |
| d2     | l2   | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 |  3 | d1     | l1   | 2015-01-01 00:00:00 | 2015-01-01 00:05:00 |  1 |
| d1     | l1   | 2015-01-01 00:00:00 | 2015-01-01 00:05:00 |  1 | d2     | l2   | 2015-01-01 00:00:01 | 2015-01-01 00:00:02 |  2 |
| d1     | l1   | 2015-01-01 00:00:00 | 2015-01-01 00:05:00 |  1 | d2     | l2   | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 |  3 |

many thanks :-)

Upvotes: 1

Views: 50

Answers (2)

Uueerdo
Uueerdo

Reputation: 15951

You are getting duplicates because your a.id != b.id allows comparing the rows in both "orders", by changing it to a.id < b.id you can allow only "earlier" records to be compared with later ones.

Edit: removed "side note", I misread the overlap condition.

Upvotes: 1

Barmar
Barmar

Reputation: 781300

Change the ID comparison in the ON clause so it only selects one of the two orderings:

ON a.id < b.id AND a.down_time <= b.up_time AND a.up_time >= b.down_time

Upvotes: 1

Related Questions