joining multiple tables

[Former Member]

how to join 5 tables kna1,knvv,knb1,knvi and knvp all of customer master .I appreciate your response

[Former Member]

Hi,

Write the select query based on the following psudo code.

Add your new fields in Where condition as well on extraction fields and join condition as per your requirement.

select a~<field1>

b~<field2>

c~<field3>

d~<field4>

e~<field5>

from kna1 AS a

inner join knvv as b on a_{kunnr = b}kunnr

inner join knb1 as c on a_{kunnr = c}kunnr

inner join knvi as d on a_{kunnr = d}kunnr

inner join knvp as e on a_{kunnr = e}kunnr

where a~kunnr in s_kunnr AND

<cond2> OR

etc,..

If you need more detail let me know your complete requirements.

thanks,

BtReddy

Message was edited by: B t

[Former Member]

Hi Priya,

1) Join on more than 2 tables is always leads to low performance.

2) Its always better to perfrom a FOR ALL ENTRIES option.

3) Have a check whether internal table is empty or not before performing a FOR ALL ENTRIES option.

The below code has join upto 3 levels. This may help you in building join upto 5 levels.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Thanks,

Vinay

[Former Member]

Hi,

Write the select query based on the following psudo code.

Add your new fields in Where condition as well on extraction fields and join condition as per your requirement.

select a~<field1>

b~<field2>

c~<field3>

d~<field4>

e~<field5>

from kna1 AS a

inner join knvv as b on a_{kunnr = b}kunnr

inner join knb1 as c on a_{kunnr = c}kunnr

inner join knvi as d on a_{kunnr = d}kunnr

inner join knvp as e on a_{kunnr = e}kunnr

where a~kunnr in s_kunnr AND

<cond2> OR

etc,..

If you need more detail let me know your complete requirements.

thanks,

BtReddy

Message was edited by: B t

[Former Member]

Hi Priyanka,

Try this way.

select a_{f1 b}f2 c_{f3 D}F4 E~F5

into table itab

from kna1 as a inner join knvv as b on a_{f1 = b}f1

inner join knb1 as c on a_{f1 = c}f1

inner join knvi as d on a_{f1 = d}f1

inner join knvp as e on a_{f1 = e}f1

where a~f1 = parameter.

Regards,

Vivek