topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Thu, 17 Aug 2006 12:14:04 GMT

hi experts,

i have to write inner join for theree tables mara,marc,makt comman field MATNR(primary key)

how to write Could u please help me

Thanks

siri

Re: Inner join

dani_mn — Thu, 17 Aug 2006 12:16:35 GMT

HI,

Inner join is simple join so you can use both key words 'JOIN' or 'INNER JOIN'. both are same.

if you want to write outer join then you have to mention 'LEFT OUTER JOIN'.

   select bs~belnr bs~bukrs bs~gsber
             bs~kostl bs~budat bs~hkont
             bs~dmbtr bs~shkzg bs~buzei
             bs~gjahr fm~fonds
      into table fw_expense_temp
      from bsis as bs
<b>      inner join fmifiit as fm</b>
      on ( bs~hkont = fm~hkont and
           bs~bukrs = fm~bukrs and
           bs~kostl = fm~fistl and
           bs~belnr = fm~knbelnr )
<b>      inner join zrange as zr</b>
      on ( bs~gsber = zr~gsber and
           bs~bukrs = zr~bukrs )
      where
        bs~hkont in hkt and
        bs~budat >= prev_yr_st_date and
        bs~budat <= curr_yr_end_date and
        bs~bukrs in bukrs and
        prctr in
        ( select prctr
          from zprofit_valid
          where uname = sy-uname ).

Regards,

HRA

Message was edited by: HRA

Re: Inner join

Former Member — Thu, 17 Aug 2006 12:17:50 GMT

Hi,

Consider you have to fetch material, desc., and plant details :

Parameters : p_matnr like mara-matnr.

select m_{matnr md}maktx w_{werks into corresponding fields of table it_mara from mara as m inner join marc as w on m}matnr = w_{matnr inner join makt as md on m}matnr = md_{matnr where m}matnr = P_MATNR and md~spras = 'EN'.

check sy-sybrc = 0.

Reward if convinced with the answer.

regards

Re: Inner join

Former Member — Thu, 17 Aug 2006 12:18:30 GMT

select mara~matnr

mara~<some field in mara>

marc~<some field in marc>

makt~<some field in makt>

from mara inner join marc

on mara_{matnr = marc}matnr

inner join makt

on mara_{matnr = makt}matnr

into corresponding fields of table it_data

where matnr in s_matnr...

Re: Inner join

Former Member — Thu, 17 Aug 2006 12:20:45 GMT

Hi,

select * into wa

from ( mara as m inner join marc as mc

on m_{matnr = mc}matnr )

inner join makt as mk

on m_{matnr = mk}matnr

Regards,

Sowjanya

Re: Inner join

Former Member — Thu, 17 Aug 2006 12:21:19 GMT

Hi,

Check this..

http://www.sap-img.com/abap/inner-joins.htm

Cheers

Re: Inner join

Former Member — Thu, 17 Aug 2006 12:22:44 GMT

Hi,

   parameters: p_matnr type mara-matnr.
data: begin of itab occurs 0,
      matnr like mara-matnr,
      werks like marc-werks,
      maktx like makt-maktx,
       end of itab.
select mara~matnr
       marc~werks
       makt~maktx
       from mara inner join marc
       on mara~matnr = marc~matnr
       inner join makt
       on mara~matnr = makt~matnr
       into table itab
       where mara~matnr = p_matnr
         and makt~spras = 'EN'.

Regards

vijay

Re: Inner join

Former Member — Tue, 12 Sep 2006 09:59:24 GMT

....

Re: Inner join

Former Member — Tue, 12 Sep 2006 10:11:18 GMT

http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

Message was edited by: Khimavath Vikranth