topic Re: Inner Join issue in Application Development and Automation Discussions

Inner Join issue

Former Member — Fri, 22 Sep 2006 06:24:49 GMT

Hi everybody,

Can we inner join 4 tables at a time.

i have used the following code,

select a_{matnr a}mtart b_{werks b}lgort c_{abcin d}iblnr

into table rep_data from

mara as a inner join

mard as b on a_{matnr = b}matnr inner join

marc as c on c_{matnr = a}matnr inner join

iseg as d on d_{matnr = a}matnr

where a~matnr in s_matnr

and a~mtart in s_mtart

and b~werks in s_werks

and b~lgort in s_lgort

and c~abcin in s_abcin.

Is It correct, or changes to be done or

iS there any code to do this.

Thanks,

Rajesh.

Re: Inner Join issue

anversha_s — Fri, 22 Sep 2006 06:29:32 GMT

hi,

chnage ur code like blow,

look the bold part

select a_{matnr a}mtart b_{werks b}lgort c_{abcin d}iblnr

into table rep_data

from

mara as a

inner join

mard as b

on a_{matnr = b}matnr

inner join

marc as c

on <b>a_{matnr = c}matnr</b>

inner join

iseg as d on <b>a_{matnr = d}matnr</b>

where a~matnr in s_matnr

and a~mtart in s_mtart

and b~werks in s_werks

and b~lgort in s_lgort

and c~abcin in s_abcin.

rgds

anver

mark hlpful answers

Re: Inner Join issue

former_member186741 — Fri, 22 Sep 2006 06:47:03 GMT

for efficiency you have to join at each level and work down the hierarchy mara->marc->mard:

select mara_{matnr mara}mtart marc_{werks mard}lgort marc_{abcin d}iblnr

into table rep_data from

mara inner join

marc on marc_{matnr = mara}matnr

inner join

mard on mard_{matnr = a}matnr

and mard_{werks = marc}werks

inner join

iseg as d on d_{matnr = a}matnr

where mara~matnr in s_matnr

and mara~mtart in s_mtart

and marc~werks in s_werks

and marc~abcin in s_abcin

and mard~lgort in s_lgort

Re: Inner Join issue

Former Member — Fri, 22 Sep 2006 07:08:00 GMT

u can use any number of tables but should have unique key.I have use 5 tables.

also give as many condns as possible.

Re: Inner Join issue

Former Member — Fri, 22 Sep 2006 08:35:05 GMT

Yes, you can join 4 tables but in your case would advice to leave out ISEG, since you are trying to link with a non-primary key...the better soln would be to get the information for material from mara/marc/mard into an internal table.

Later use the same with FOR ALL ENTRIES to fetch data from ISEG alongwith Material/Plant and Storage location information.

Hope the above helps !!!