Multiple Table Join instead of Nested Selects?
I am in the process of writing a program that basically uses 6 tables. The first table holds information related to the second and the second holds info for the other 4 tables. For example, table 1 holds a key used in table 2. Table 2 holds keys used in tables A1 and A2, and also keys used in tables B1 and B2.
In tables A1, A2, B1, and B2, there can be multiple records related to the keys from table 2. For example, A1 might have 3 records, A2 might have 1 record, B1 might not have any records, and B2 might have 6 records. I'll need all the records in an internal table related to the keys from table 2.
My original extract runs very slow and is similar to this high level "pseudo-code":
select keyA keyB from table2 where table1 inner join with table2. select value from tableA1 where keyA = table2-keyA. * do some calculations, add to internal table endselect. select value from tableA2 where keyA = table2-keyA. * do some calculations, add to internal table endselect. select value from tableB1 where keyB = table2-keyB. * do some calculations, add to internal table endselect. select value from tableB2 where keyB = table2-keyB. * do some calculations, add to internal table endselect. endselect.
Essentially what I want to do is grab keys from table 2, look in table A1 and get all related records. Then look in table A2, and grab those related records. Then look in table B1, and grab those related records. Then look in table B2, and grab those related records.
How would it be possible to implement this without using nested selects? All tables are rather large so performance is a big deal. I know I'm supposed to avoid nested selects but conceptually this was an easy way to look at it. I've started on a new select statement that does joins but it's not producing the results I'm looking for as it tends to duplicate various records (for example, it'll grab from A1 all three of it's records, but then repeat A2's 1 record for each of A1's 3 records).
Any help would be greatly appreciated and points will be awarded to a working solution. I hope I haven't confused anyone. Thanks in advance.
Amit Mittal replied
1. My personal recommendation would be somewhat like this :
a) Since this join involves almost six tables,
a single sql is not good.
b) Instead of using select-endselect,
we should use Select ,, into TABLE itab.
c) then we should ,
select from A FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1
select from B FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1
select from C FOR ALL ENTRIES WHERE ITAB-FIELD = FIEDL1
d) then we should
Loop at ITAB.
read internal table A with key.
read internal table B with key.
2. Using this approach would
MINIMIZE DATABASE READS.