MySQL - How to choose "DISTINCT" overlap periods (dates or number ranges)

Question

Put it briefly, if the query tells me A overlaps B, then I don't need this to also tell me that B also overlaps A when they overlap.

So I'm trying to use self join in sql to select the "DISTINCT" overlap.

To illustrate, here is a simple SQL script that I wrote to display the included overlap selection ( http://sqlfiddle.com/#!9/7af84f/1 )

More details ...

Suppose I have a table of names (char), d1 (int), d2 (int), the circuit of which is below. Here d1 and d2 represent the beginning and end of a certain interval, which may overlap with another interval in the same table,

CREATE TABLE test (
  letter char ,
  d1 int ,
  d2 int  
) ;

,

INSERT INTO test (letter,d1,d2)
VALUES
   ('A',  2, 10),    -- overlaps C and D
   ('B', 12, 20),    -- overlaps E
   ('C',  5, 10),    -- overlaps A and D
   ('D',  1,  8),    -- overlaps A and C 
   ('E', 13, 15),    -- overlaps B
   ('F', 25, 30);    -- doesn't overlap anything

, , d1 d2 d1 d2 .

-- selects all records that overlap in the range d1 - d2 inclusive
-- (excluding the implicit overlap between a record and itself)
-- The results are sorted by letter followed by d1

SELECT
  basetable.letter as test_letter,
  basetable.d1,
  basetable.d2,
  overlaptable.letter as overlap_letter,
  overlaptable.d1 as overlap_d1,
  overlaptable.d2 as overlap_d2

FROM
  test as basetable, 
  test as overlaptable
WHERE
  -- there is an inclusive overlap
  basetable.d1 <= overlaptable.d2 and basetable.d2 >= overlaptable.d1
AND
  -- the row being checked is not itsself
    basetable.letter <> overlaptable.letter
    AND
    basetable.d1 <> overlaptable.d1
    AND 
    basetable.d2 <> overlaptable.d2
ORDER BY 
  basetable.letter,
  basetable.d1

, 6 , , , A C, , C A ( , sqlfiddle, , , )

test_letter     d1     d2   overlap_letter  overlap_d1  overlap_d2
  A              2     10         D              1         8
  B             12     20         E             13        15
  C              5     10         D              1         8
  D              1      8         A              2        10
  D              1      8         C              5        10
  E             13     15         B             12        20

:

sql, "DISTINCT" " "?

.. ...

test_letter  d1     d2  overlap_letter  overlap_d1  overlap_d2 
    A         2     10        D            1           8
    A         2     10        C            5          10
    B        12     20        E           13          15
    C         5     10        D            1           8

:
 , A, B C

• A (2,10) D (1,8) C (5,10) { ROWS}
• B (12,20) E (13,15) { }
• C (5,10) D (1,8) { , A (1,10), 2 , A C}
• D (1,8) { , A (1,10) C (5,10)}
• E (13,15) { , B (12,20)}
• F (25,30) { , }

Answer 1

. JOIN:

SELECT basetable.letter as test_letter, basetable.d1, basetable.d2,
       overlaptable.letter as overlap_letter, overlaptable.d1 as overlap_d1, overlaptable.d2 as overlap_d2
FROM test basetable JOIN
     test overlaptable
     ON basetable.d1 <= overlaptable.d2 AND
        basetable.d2 >= overlaptable.d1
WHERE basetable.letter < overlaptable.letter  -- This is the change
ORDER BY basetable.letter, basetable.d1;

Answer 2

, . .

CREATE TABLE test (
  letter char ,
  d1 int ,
  d2 int  
) ;

INSERT INTO test (letter,d1,d2)
VALUES
   ('A',  2, 10),    -- overlaps C and D
   ('B', 12, 20),    -- overlaps E
   ('C',  5, 10),    -- overlaps A and D
   ('D',  1,  8),    -- overlaps A and C 
   ('E', 13, 15),    -- overlaps B
   ('F', 25, 30),    -- doesn't overlap anything
   ('G', 50, 60),    -- a set of equal intervals
   ('H', 50, 60),
   ('I', 50, 60)


SELECT
  basetable.letter as test_letter,
  basetable.d1,
  basetable.d2,
  overlaptable.letter as overlap_letter,
  overlaptable.d1 as overlap_d1,
  overlaptable.d2 as overlap_d2

FROM
  test as basetable, 
  test as overlaptable
WHERE
  -- there is an inclusive overlap
  basetable.d1 <= overlaptable.d2 and basetable.d2 >= overlaptable.d1
AND
  -- require lexicographic order: basetable starts later / finishes earlier / its letter is less then overlaptable
  basetable.d1 > overlaptable.d1 OR (basetable.d1 = overlaptable.d1 
                                     AND (basetable.d2 < overlaptable.d2 OR (basetable.d2 = overlaptable.d2 
                                                                             AND basetable.letter < overlaptable.letter)))
ORDER BY 
  overlaptable.d1, 
  basetable.d2,
  basetable.letter