joining 4 tables using join

[Former Member]

hi gurus,

pls i know how to use join using 3 tables.but i want to know how can we achieve this using 4 table.pls send me the syntax for joining 4 tables.

thanks and regards,

Rajesh.

[Former Member]

Hi,

Why dont you create Views and then use the view in your SELECT query.

Generic example of JOINING 4 tables

SELECT FLD1 FLD2 FLD3 FLD4 FLD5 INTO CORRESPONDING FIELDS OF TABLE ITAB FROM TABLE1 INNER JOIN TABLE2 ON TABLE1_{FLD1 = TABLE2}FLD1 INNER JOIN TABLE3 ON TABLE2_{FLD2 = TABLE3}FLD2 INNER JOIN TABL4 ON TABLE3_{FLD3 = TABLE4}FLD3 WHERE FLD1 = 'AA'

Reward if useful

[Former Member]

Hi rajesh,

you can also join 4 tables as you do it with three..

but let me inform this is not performance intensive..

for example refer this,

SELECT i~tplnr

f~pltxt

i~invnr

i~objnr

i~erdat

i~fltyp

i~iwerk

a~class

k~clint

c~atnam

c~atinn

INTO CORRESPONDING FIELDS OF TABLE out_tab2

FROM ( ( ( iflot AS i INNER JOIN iflo AS f ON f_{tplnr = i}tplnr )

INNER JOIN kssk AS k ON k_{objek = i}tplnr )

INNER JOIN klah AS a ON a_{clint = k}clint )

INNER JOIN cabn AS c ON c_{clint = k}clint )

INNER JOIN cawn AS w ON w_{atinn = c}atinn )

pls reward if helps,

regards.

[Former Member]

Hi,

it is not advisable to use join for more than 3 tables. So instead of going for join here,u can use some other alternative e.g use FOR ALL ENTRIES.

Rupesh

[Former Member]

Hi,

You can join the 4 tables using join. This code you can adopt.

 ********Selection from ANEK, ANEP, ANLA, ANLZ

  SELECT   anek~bukrs
           anek~anln1
           anek~anln2
           anek~gjahr
           anek~lnran
           anek~obart
           anek~objid
           anek~awtyp
           anek~xblnr
           anek~aworg
           anek~budat
           anek~cpudt
           anek~usnam
           anek~zuonr
           anek~anlu1
           anek~anlu2
           anep~bwasl
           anep~anbtr
           anep~afabe
           anla~ktogr
           anla~anlkl
           anla~ord43
           anlz~kostl
           anlz~gsber

           INTO TABLE t_final
           FROM anek
           INNER JOIN anep
           ON ( anek~bukrs = anep~bukrs AND
                anek~anln1 = anep~anln1 AND
                anek~anln2 = anep~anln2 AND
                anek~gjahr = anep~gjahr AND
                anek~lnran = anep~lnran )
           INNER JOIN anla
           ON ( anek~bukrs = anla~bukrs AND
                anek~anln1 = anla~anln1 AND
                anek~anln2 = anla~anln2 )
           INNER JOIN anlz
           ON ( anek~bukrs = anlz~bukrs AND
                anek~anln1 = anlz~anln1 AND
                anek~anln2 = anlz~anln2 )

          WHERE anek~bukrs IN s_bukrs  AND
                anek~gjahr = p_gjahr   AND
                anek~budat IN s_budat  AND
                anep~afabe = p_afabe   AND
                anla~anlkl IN s_anlkl  AND
                anla~xanlgr = p_xanlgr AND
                anlz~bdatu  = p_bdatu.  

Thanks

Sarada

[Former Member]

Hi,

Performance wise, I would rather recommend you to use a Select Query using For all entries,Make sure that you enter the next Selct Query using sy-subrc check such that you get matching related records at one-shot.

The difference between an INNER JOIN and an OUTER JOIN is the following. If a query on an INNER JOIN of VBAK (outer table) and VBAP (inner table) finds a record in VBAK but no matching records in VBAP, then no data is retrieved from the database because the inner table is empty. If you still want to keep VBAK rows for which there are no matching VBAP rows, you need to use the OUTER JOIN construct available in ABAP/4 Open SQL in 4.x..

Regards,

Rohini.

[Former Member]

Thanks