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

admin管理员组

文章数量:1134232

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?

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?

• sql
• oracle-database

Share Improve this question Follow edited Jan 7 at 19:49 Dale K 27.1k1515 gold badges5555 silver badges8282 bronze badges asked Jan 7 at 18:42 AnnonymousAskerAnnonymousAsker 4155 bronze badges 3

• 1 Can you please change the images to tabular markdown format data in text? – samhita Commented Jan 7 at 19:18
• 2 As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Commented Jan 7 at 19:49
• I don't understand the example. It looks like for each item you want the start-end-range sections the in-depth to out-depth range overlap with. 1=A, 2=A,B,C, 3=C,D, 4=D,E. But your result only shows D for item 4. And you mention the specifed_depth, but I don't see how this is involved. – Thorsten Kettner Commented Jan 8 at 8:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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"

本文标签： Oracle SQL Join Two Tables With 2 Interval Ranges and ConditionStack Overflow