Search code examples
sqloracle-database

Oracle SQL- Join Two Tables With 2 Interval Ranges and Condition

I have a Main Table that I would like to add the "Goal" column to, which will identify what section an item has gone through.

Table 1 stores separate data that labels the section and establishes a start and end depth. However, I have a Specified Depth column that will control the final depth that an item will go through.

The "Goal" is to be able to effectively label what section/s the item has been through. I've thought about pivoting Table 1, but sometimes there are many sections, so it would be very difficult to code for a pivot on every occasion. Is there a way that I'm not thinking of that could lead me to getting that "Goal" column in there and be able to join Table 1 to the Main Table?

Example

Solution 
  • SELECT * 
FROM   "Main table" AS MT
       JOIN "Table 1" AS T1
          ON T1.Start BETWEEN MT."In Depth" and MT."Out Depth" OR                      
             T1.Eend  BETWEEN MT."In Depth" and MT."Out Depth"

    With aggregation :

    SELECT MT.*, LISTAGG(SECTION, ',') AS Goal
FROM   "Main table" AS MT
       JOIN "Table 1" AS T1
          ON T1.Start BETWEEN MT."In Depth" and MT."Out Depth" OR                      
             T1.Eend  BE, TWEEN MT."In Depth" and MT."Out Depth"
GROUP  BY Location, Item"In Depth", "Out Depth", "Sepcuified Depth"