topic joining multiple tables in Application Development and Automation Discussions

joining multiple tables

Former Member — Tue, 29 Aug 2006 20:03:09 GMT

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

Re: joining multiple tables

Former Member — Tue, 29 Aug 2006 20:13:16 GMT

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

Re: joining multiple tables

Former Member — Tue, 29 Aug 2006 20:15:19 GMT

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

Re: joining multiple tables

Former Member — Tue, 29 Aug 2006 20:16:22 GMT

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