In a select sub query, how to select a value from one row, then select a value from the next row, and so forth?

2
votes

Relating to my example, I'm trying to add the RANGE column values of multiple rows in TABLE1 where the SOURCE column value of each of those rows equals the ID column value of different rows in TABLE1 of which the PARENT column value of those different rows equals an ID column value in TABLE2. I've drawn a diagram of sorts to visually display this, too.

Diagram of sorts

I'm having trouble selecting only one row TABLE2.ID value at a time to plug into the Select sub query, then, main select needs to run again and again using the next row's TABLE2.ID value. There are thousands of rows in each table. I've played around with fetch and lag but I'm really not knowledgeable enough to figure it out currently. I am still researching how.... As-is below, I believe it is wanting to add all of the RANGE values of all TABLE1 rows since the sub query's select ID is reading as all of the rows in TABLE2 (where TABLE1.SOURCE=TABLE2.ID)?

Select
  ID,
  (Select SUM(RANGE)
    from TABLE1
    where SOURCE in
      (Select A.ID
       from TABLE1 A, TABLE2 B
       where A.PARENT = B.ID
      )
   ) as "SUM(RANGE)"
from TABLE2
______________
Result:
______________
ID    SUM(RANGE)
A10    18
A11    18

Expected result:

ID    SUM(RANGE)
A10    13
A11    5

Example table data:

 TABLE1
 ID    RANGE    SOURCE    PARENT
 A1                       A10
 A2                       A10
 A3                       A10
 A4                       A11
 A5                       A11
         2        A1         
         5        A2         
         6        A3         
         1        A4         
         4        A5         
 ---------------------------------
 TABLE2
 ID
 A10
 A11
2
sqloracle
Could you provide some sample data and expect result? - D-Shih
I've now updated my question with sample data and the expected result. - Brian
How could you get your expect result 13? and why did you need to design your table like this. It seem can split for two tables from table1 - D-Shih
Updated my question. Expected result is a row with 13 and a row with 5. I could get one row at a time if say for the TABLE1.PARENT equal a specific ID of TABLE1, but I need to do that for thousands of rows. - Brian

2 Answers

1
votes

You can try to self-join in TABLE1 by ID and SOURCE, and do SUM and GROUP BY

SELECT t1.parent,SUM(ts.range) as "SUM(RANGE)"
FROM TABLE1 t1 
JOIN TABLE1 ts on t1.ID = ts.SOURCE and t1.SOURCE IS NULL
WHERE  t1.parent in (SELECT ID FROM TABLE2) 
GROUP BY t1.parent
1
votes

I suppose you mean such a statement :

Select a1.parent as ID, sum(a2.range) as "SUM(RANGE)"
  From table1 a1 
  Left Join table1 a2 on ( a2.source = a1.id )
 Inner Join table2 b  on ( a1.parent =  b.id )
 Group By a1.parent
 Order By a1.parent;

that containing a self-join in it.

Rextester Demo