topic Re: join on 3 tables using inner join. in Application Development and Automation Discussions

join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:17:31 GMT

Hi friends can one one correct the below code and send me back.

its showing some error.

SELECT a~matnr

a~werks

b~maktx

c~prctr

c~bklas

C~stprs

c~peinh

INTO TABLE it_detail

FOR ALL ENTRIES IN itab

FROM marc AS a INNER JOIN makt AS b

ON a_{matnr = b}matnr

inner join mbew AS c

WHERE a~matnr = itab-matnr

and werks = itab-werks.

Re: join on 3 tables using inner join.

former_member745780 — Tue, 19 Aug 2008 11:23:30 GMT

Hello

try this


SELECT a~matnr
a~werks
b~maktx
c~prctr
c~bklas
C~stprs
c~peinh
INTO TABLE it_detail
FOR ALL ENTRIES IN itab
FROM marc AS a INNER JOIN makt AS b
ON a~matnr = b~matnr
inner join mbew AS c
ON b~matnr = c~matnr
WHERE a~matnr = itab-matnr
and werks = itab-werks.

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:26:38 GMT

anirudh check that code and send it once again

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:34:55 GMT

Hi,

Try this.

SELECT a~matnr

a~werks

b~maktx

c~prctr

c~bklas

C~stprs " c is in CAPS change it

c~peinh

INTO TABLE it_detail

FOR ALL ENTRIES IN itab

FROM marc AS a INNER JOIN makt AS b

ON a_{matnr = b}matnr

inner join mbew AS c

WHERE a~matnr = itab-matnr

and werks = itab-werks.

Sharin.

Re: join on 3 tables using inner join.

former_member745780 — Tue, 19 Aug 2008 11:36:41 GMT

add this for compare b and c

ON b~matnr = c~matnr

hope this will help you

otherwise let me know the error.

and check your where clause

Thanks

Anirudh

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:36:47 GMT

Hi,

Pls find the below piece of code.

begin of it_detail occurs 0,

matnr like mara-matnr,

werks like marc-werks,

prctr like marc-prctr,

maktx like makt-maktx,

bklas like mbew-bklas,

stprs like mbew-stprs,

peinh like mbew-peinh,

end of it_detail.

SELECT a~matnr

a~werks

a~prctr

b~maktx

c~bklas

C~stprs

c~peinh

INTO TABLE it_detail

FROM marc AS a INNER JOIN makt AS b

ON a_{matnr = b}matnr

inner join mbew AS c

on a_{matnr = c}matnr

FOR ALL ENTRIES IN itab

WHERE a~matnr = itab-matnr

and a~werks = itab-werks.

if it is not working, pls send me the error message that you are getting.

Thanks,

Sreeram.

Edited by: Sreeram Prasad on Aug 19, 2008 5:07 PM

Edited by: Sreeram Prasad on Aug 19, 2008 5:12 PM

Re: join on 3 tables using inner join.

former_member541575 — Tue, 19 Aug 2008 11:38:09 GMT

you can try this

SELECT a~matnr

a~werks

b~maktx

c~prctr

c~bklas

C~stprs

c~peinh

INTO TABLE it_detail

FOR ALL ENTRIES IN itab

FROM marc

INNER JOIN makt AS b

ON a_{matnr = b}matnr

inner join mbew AS c

ON a_{matnr = c}matnr

WHERE a~matnr = itab-matnr

and werks = itab-werks.

u can take help of this

SELECT ekko~ebeln

ekko~ekorg

ekko~aedat

ekko~lifnr

lfa1~name1

ekpo~ebelp

ekpo~matnr

ekpo~werks

ekpo~txz01

ekpo~menge

INTO TABLE i_ekko

FROM ekko

INNER JOIN ekpo ON ekko_{ebeln = ekpo}ebeln

INNER JOIN lfa1 ON ekko_{lifnr = lfa1}lifnr

WHERE ekko~ekorg IN s_ekorg AND

ekko~ebeln IN s_ebeln AND

ekko~aedat IN s_aedat.

Regards

Atul

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:42:01 GMT

Hi ,

Try this .

first please check whether the table ITAB has entries in it. If there are no entries , this select will fail.

if not itab[] is initial.

SELECT a~matnr

a~werks

b~maktx

c~prctr

c~bklas

C~stprs

c~peinh

INTO TABLE it_detail

FOR ALL ENTRIES IN itab

FROM marc AS a INNER JOIN makt AS b

ON a_{matnr = b}matnr

inner join mbew AS c

on b_{matnr = c}matnr

WHERE a~matnr = itab-matnr

and a~werks = itab-werks.

endif.

Regards,

Chitra

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:50:03 GMT

Hi ,

You can try the following code.

select a~matnr

a~werks

b~maktx

c~prctr

c~bklas

c~stprs

c~pein

INTO TABLE IT_DETAIL

FOR ALL ENTRIES IN IT_TAB

FROM MARC AS a INNER JOIN MAKT AS b

ON a_{MATNR = b}MATNR

INNER JOIN MBEW AS c

ON a_{MATNR = c}MATNR

WHERE a_{matnr = it_tab}matnr

and

werks = it_tab-werks .

This query is going to fetch the correct data.Because we are filtering the data based on the MATNR(material number) to the maximum extent .

Regards

Navy

Re: join on 3 tables using inner join.

Mohamed_Mukhtar — Tue, 19 Aug 2008 11:51:47 GMT

Try to do like this...

SELECT a~matnr

a~werks

b~maktx

c~prctr

c~bklas

C~stprs

c~peinh

INTO TABLE it_detail

FOR ALL ENTRIES IN itab

FROM ( (marc AS a INNER JOIN makt AS b

ON a_{matnr = b}matnr )

inner join mbew AS c )

WHERE a~matnr = itab-matnr

and werks = itab-werks.

With Regards.

Always Learner

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 11:56:02 GMT

Hi Priyanka,

1]You dont have field by name prctr in MBEW Table please correct this first

2]When you are declaring WERKS field in Internal Table declare this of type WERKS_D

3] Add B_{MATNR = C}MATNR in your join statement as shown below.

SELECT a~matnr

a~werks

b~maktx

c~bklas

c~stprs

c~peinh

INTO TABLE it_detail

FROM marc AS a

INNER JOIN makt AS b

ON a_{matnr eq b}matnr

inner join mbew AS c

on b_{matnr eq c}matnr

FOR ALL ENTRIES IN itab

WHERE a~matnr eq itab-matnr

and a~werks eq itab-werks.

Thanks

Sudharshan

Re: join on 3 tables using inner join.

Former Member — Tue, 19 Aug 2008 12:22:03 GMT

Hi,

Pls find the below piece of code.

begin of it_detail occurs 0,

matnr like mara-matnr,

werks like marc-werks,

prctr like marc-prctr,

maktx like makt-maktx,

bklas like mbew-bklas,

stprs like mbew-stprs,

peinh like mbew-peinh,

end of it_detail.

SELECT a~matnr

a~werks

a~prctr

b~maktx

c~bklas

C~stprs

c~peinh

INTO TABLE it_detail

FROM marc AS a INNER JOIN makt AS b

ON a_{matnr = b}matnr

inner join mbew AS c

on a_{matnr = c}matnr

FOR ALL ENTRIES IN itab

WHERE a~matnr = itab-matnr

and a~werks = itab-werks.