topic Re: How to join two different tables into internal table using one select statement . in Application Development and Automation Discussions

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

Former Member — Wed, 24 Apr 2013 11:31:36 GMT

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.

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

Former Member — Wed, 24 Apr 2013 11:42:33 GMT

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

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

Former Member — Wed, 24 Apr 2013 11:55:05 GMT

This message was moderated.

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

Former Member — Wed, 24 Apr 2013 12:11:42 GMT

This message was moderated.

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

Former Member — Wed, 24 Apr 2013 12:47:05 GMT

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.

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

Former Member — Wed, 24 Apr 2013 12:51:43 GMT

This message was moderated.

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

custodio_deoliveira — Wed, 24 Apr 2013 13:00:18 GMT

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

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

Former Member — Wed, 24 Apr 2013 13:01:52 GMT

This message was moderated.

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

Former Member — Wed, 24 Apr 2013 13:06:20 GMT

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.

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

Former Member — Wed, 24 Apr 2013 14:25:48 GMT

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 .

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

Clemenss — Wed, 24 Apr 2013 14:31:14 GMT

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

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

Former Member — Wed, 24 Apr 2013 14:39:23 GMT

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.

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

Clemenss — Thu, 25 Apr 2013 07:55:08 GMT

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

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

custodio_deoliveira — Thu, 25 Apr 2013 13:10:24 GMT

Completely disagree with the non alias stuff.

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

Former Member — Fri, 26 Apr 2013 10:28:52 GMT

Hi,

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

regards

Dinesh

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

anand_n5 — Sat, 15 Mar 2014 14:11:16 GMT

This message was moderated.

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

Former Member — Sun, 16 Mar 2014 10:24:23 GMT

Hi Anand,

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

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

anand_n5 — Sat, 22 Mar 2014 18:00:53 GMT

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.

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

Former Member — Sat, 22 Mar 2014 18:49:32 GMT

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.