There are four case make the overlap exists
(ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end):

1. ns - ne - es - ee: doesn't overlap and doesn't match (because ne < es)
2. ns - es - ne - ee: ovarlaps and matches
3. es - ns - ee - ne: ovarlaps and matches
4. es - ee - ns - ne: doesn't overlap and doesn't match (because ns > ee)
5. es - ns - ne - ee: ovarlaps and matches
6. ns - es - ee - ne: ovarlaps and matches

SQL Statment Below method is found from internet while the second method is my own way usally use.

SELECT * FROM tbl WHERE
existing_start BETWEEN '$newStart' AND '$newEnd' OR
existing_end BETWEEN '$newStart' AND '$newEnd' OR
'$newStart' BETWEEN existing_star AND existing_end

SELECT * FROM tbl WHERE
( existing_start<='$newStart' AND existing_end>'$newStart' )
AND ( existing_start<'$newEnd' AND existing_start>'$newStart' )

Today learn a new method on MySQL. On programing, we always need to judge a record is exists or not, update when exists else insert. I'm always using PHP to do that before, but today I find the that MySQL already have a method to do so. For example, if a table contain three colum a,b,c and a is the unique key or primary key, you can use the following statment to do an update exists else insert.

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

This will insert a new record when the table hasn't a record a=1 else the record will update c=c+1

Table1

Table2

I want to get a result as following:

In this case, if we use LEFT JOIN the result will fetch to 5 columns data, if we use GROUP BY, it can come back with 2 columns of data but the row "value" can only come up with either 1 value of the grouped row due the order priority. So, how can we make the expected result?