CODEWITHSUNDEEP
sql
Caerus
Caerus

Reputation: 674

SQL Conditional Union from One Table

I have a table that looks like this:

tblUser

   id | name    | start_Date | end_date
    1 | anna    | 2018-01-01 | 2018-06-30
    2 | benzema | 2018-02-01 | 2018-05-30
    3 | bale    | 2018-02-14 | 2018-02-24
    4 | kroos   | 2019-03-14 | 2019-10-21

I would like to select the names with overlapping start and end dates and return a result similar to the one below, where the first column is name that corresponds to the lower-valued id:

     name1      | name2
   | anna       | benzema
   | anna       | bale
   | benzema    | bale

I took a look at the conditional union answer here, but it's not quite right because I am using a single table rather than two tables. Would it be correct to set up a conditional union using two different aliases for tblUser?

Upvotes: 1

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I believe the correct logic is:

SELECT u1.name AS name1, u2.name AS name2
FROM tblUser u1 JOIN
     tblUser u2
     ON u1.start_date < u2.end_date AND
        u1.end_date > u2.start_date AND
        u1.id < u2.id
ORDER BY u1.id;

Two periods overlap when one starts before the second ends and the first ends after the second starts.

Upvotes: 0

Nick
Nick

Reputation: 147146

A simple JOIN query should suffice, joining tblUser to itself when the start and end dates overlap between the two tables:

SELECT t1.name AS name1, t2.name AS name2
FROM tblUser t1
JOIN tblUser t2 ON (t2.start_Date BETWEEN t1.start_Date and t1.end_date 
                OR t2.end_date BETWEEN t1.start_Date and t1.end_date)
                AND t2.id != t1.id
ORDER BY t1.id

Output:

name1       name2
anna        benzema
anna        bale
benzema     bale

Demo on SQLFiddle

Upvotes: 2

Related Questions