How to join two different tables into internal table using one select statement .

[Former Member]

Hi,
This is my scenario

I've two tables MCH1 & MCHB .

I've 3 fields that has to be selected from MCH1 table with MATNR as key & I've to select 2 fields from MCHB with MATNR as key field.

The above condition has to be done by using only one select statement and data has to be stored in internal table in xxx.

[Former Member]

Hi,

     It can be achieved using JOIN in SELECT statement. Please search in the forum to find more information about it.

Hope this helps,

~Athreya

[Former Member]

This message was moderated.

[Former Member]

This message was moderated.

[Former Member]

Hi ,

Use inner join to do so.Check sample code as below.

select  mch1~matnr

              mch1~xyz

              mchb~abc

into table it_mch1_mchb

where mch1-matnr in s_matnr.

[Former Member]

This message was moderated.

[Former Member]

Use inner join to do so.Please find correct code as below.

select    mch1~matnr

              mch1~xyz

              mchb~abc

into table it_mch1_mchb

from mch1 inner join mchb

where mch1-matnr in s_matnr.

[custodio_deoliveira]

select a~field1 a~field2 a~field3 b~field1 b~field2

         from MCH1 as a

           join MCHB as b

         on a~matnr eq b~matnr

         into table t_xxx

         where a~matnr in s_matnr "or whatever select cause you have

[Clemenss]

Hi,

and if you want that anyone understands your code, then do not use ALIAS AS a or AS b if you don't need to:

select MCH1~field1 MCH1~field2 MCH1~field3 MCHB~field1 MCHB~field2

  from MCH1

  join MCHB

  on MCH1~matnr eq MCHB~matnr

  into table t_xxx

  where MCH1~matnr in s_matnr "or whatever select cause you have

Regards

Clemens

[Former Member]

Hi Cleamens ,

Thanks for the reply.

what if I've to add multiple condition in where clause ?

like the below code

Select matnr , charg, vfdat from MCH1 table

          where MCH1-MATNR = gt_matnr_batch -MATNR

          AND mch1-lvorm <> ‘X’

         AND ZUSTD <> ‘X’.

Select field CLABS from table MCHB

            Where MATNR = gt_matnr_batch-matnr

             And werks = [input_parma]

            And lgort = [input_parma]

            And lvorm <> ‘X’

Regards

Suhas.

[Clemenss]

Hi Suhas,

you can have the where clause as you need it. Only if a field is present in both tables joined, you will get a syntax error because it can not be determined which table should be taken into consideration.

You must write WHERE MCH1~MATNR = ... or MCHB~MATNR = ... which is the same as the join condition defines. For LVORM you must decide or know if you need  WHERE MCH1~LVORM = 'X' or

MCHB~LVORM = 'X' or even combine the conditions with AND.

For transparency reasons it may be always better to specify the table, i.e. MCH1~ZUST = 'X'. It helps other people to understand the code better.

Regards

Clemens

[custodio_deoliveira]

Completely disagree with the non alias stuff.

[Former Member]

Hi,

i have also replied for this post and its been approved by moderator also then why it is not displayed.

regards

Dinesh

[Former Member]

This message was moderated.

[Former Member]

hi suhas for this the above is correct, in abap there is one select statement called select with joins which can be used to fetch the data from multiple table at a time in to one internal table . as they said the above is the syntax , but u can use this select statement only when there is atleast one common field in the joining tables for join in the join condition . here we can use either inner join or else ought er join as per your need.

select table1~field1 table1~field2--- table2~field1 table2~field2

          into table ITAB

          from table 1 alias a table 2 alias b

          inner join <join condition>

           where condition .

[anand_n5]

This message was moderated.

[Former Member]

Hi Anand,

Please send snapshots of each of the three tables as seen in se11.

[anand_n5]

Hi John

Thank you for your concern

Am using MARD,MARA,MAKT tables

MARD

matnr

werks

lgort

MARA

matnr

ersda

ernam

mtart

matkl

MAKT

matnr

spras

maktx

I have declared three internal tables now i need to move all these records to single internal table

but am not sure whether i should joins or fall all entries if you could please let me know how can i move all these records to a single internal table using SELECT statement.

[Former Member]

Hi Anand,

*One select statement seems enough.

*Try the following (copy and paste):

types: begin of mar_typ,

  matnr type mara-matnr,

  werks type mard-werks,

  lgort type mard-lgort,

  ersda type mara-ersda,

  ernam type mara-ernam,

         mtart type mara-mtart,

         matkl type mara-matkl,

  spras type makt-spras,

  maktx type makt-maktx,

       end of mar_typ.

data: int_tab type standard table of mar_typ,

       wa type mar_typ.

select a~matnr a~werks a~lgort

       b~ersda b~ernam b~mtart b~matkl

       c~spras c~maktx

   into corresponding fields of table int_tab

   from mard as a

   inner join mara as b

     on a~matnr = b~matnr

   inner join makt as c

     on b~matnr = c~matnr

where c~spras = 'EN'. "or your logon language.

loop at int_tab into wa.

write:/ wa-matnr, wa-werks, wa-lgort, wa-ersda, wa-ernam, wa-mtart, wa-matkl, wa-spras, wa-maktx.

endloop.