sql query with complicated join

I have three tables :

• The first one has three columns(ID,Category,StartDate,EndDate)
• The second one has also the same three columns (ID,Category,StartDate,EndDate)
• The third one has three columns (Key,Category1,Category2)

I have created a sql script that returns 1 if a row from the second table is within the period defined in the first table for the given category (where table1.ID = table2.ID and table1.category = table2.category).

What I would lik开发者_开发问答e to do now is to create a SQL script that checks if a row from the second table is within the period defined in the first table for the given category and id, or if is within the period defined by ID1_StartDate and ID2_EndDate (Where ID1 and ID2 are successive, meaning that they have the same category and if we put in date ascending order this category id2 is after id1) if there is such a relationship in table3 for these two categories.

---

The following query will return the table1 ID if either of these conditions are true:

1) the date range described by table 1 intersects at all with the date range in table2

2) the date range described by table 1 intersects with the date range beginning with the start date of a row in table2 and ending at the enddate of the next chronological row in table2 with the same category.

select distinct table1.id 
from table1 
inner join (select id, 
                   category, 
                   startdate, 
                   enddate,
                   lead(enddate) 
                        over (partition by category
                              order by enddate) as next_enddate
            from table2) 
on (table1.startdate < table2.enddate 
    or table1.startdate < table2.next_enddate)
   and table1.enddate >= table2.startdate
   and table1.ID = table2.ID 
   and table1.category = table2.category

I don't entirely understand the use of the third table in this scenario, so it is ignored for the time being. Hopefully this will still get you closer to the answer you're looking for.