SELECT FROM multiple tables INTO one internal Table

Question

My db tables:

db_1
db_2
db_3

My internal table:

it_comb

it_comb has a structure with some fields from db_1, db_2, db_3.

All db tables have different structures.

I want to select everything from db_1, db_2, db_3 into the correct fields of it_comb with a where condition.

I would like to do something like this: (This doesn't work)

SELECT * From db_1, db_2, db_3 into CORRESPONDING FIELDS OF TABLE it_comb WHERE db_1-MATNR LIKE db_2-MATNR AND db_1-MATNR LIKE db_3-MATNR.

Obviously, this doesn't work because I can't use ',' like that. How do I write this in ABAP? So that it_comb is filled with data from db_1, db_2 and db_3.

Another problem is that every time I select something into it_comb, my previous data gets overwritten.

Code example would be appreciated for ABAP-Beginner.

Solution 1

You can use inner join -

SELECT * APPENDING CORRESPONDING FIELDS OF TABLE it_comb
FROM db_1 AS a
INNER JOIN db_2 AS b
ON a~matnr = b~matnr
INNER JOIN db_3 AS c
ON a~matnr = c~matnr
WHERE (Your any other condition).

APPENDING won't overwrite the previous record from internal table it_comb.

Warning: Use APPENDING if internal table is TYPE STANDARD otherwise you'll get dump. Also check the SELECT - JOIN documentaion

Solution 2

One other thing you can do in the newer ABAP versions is

select * from mara inner join mvke on mvke~matnr = mara~matnr into table @data(lt_combined).    
loop at lt_combined into data(ls_combined).
  write: / ls_combined-mara-matnr, ls_combined-mvke-vkorg.
endloop.

this will define and populate your internal table in one step without the need for a separate "data" statement.

Note that in a join with *, you will get an internal table with substructures based on the table names -- since the structure is implied in the field-list of the select, you can also do something like this for a more efficient database query (so it doesn't need to return all the fields) which also eliminates the substructures:

select mara~matnr, mvke~vkorg from mara inner join mvke on mvke~matnr = mara~matnr into table @data(lt_combined).
loop at lt_combined into data(ls_combined).
  write: / ls_combined-matnr, ls_combined-vkorg.
endloop.

Hope this helps!

Comments

[divScorp]

@Lucky if your internal table already holds the data then obviously it will override the data. Better create another internal table of same structure and move that record before select statement. APPEND LINES OF it_comb TO it_comb1.

[József Szikszai]

you can use APPENDING by SELECT. This will not delete the data in the internal table, but adds the newly selected lines: SELECT ... INTO APPENDING TABLE ... or INTO APPENDING CORRESPONDING FIELDS OF TABLE ... (Obviously works only for TYPE STANDARD tables)

[divScorp]

@JozsefSzikszai Thanks for the information, I haven't used APPENDING in SELECT earlier.