topic Re: innerjoin in Application Development and Automation Discussions

innerjoin

Former Member — Tue, 22 Jan 2008 07:36:20 GMT

HI All,

Could you please let me know, how to write a inner join for 4 tables. Give me some sample code.

Akshitha.

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:38:40 GMT

Hi,

check the below example:

SELECT mara_{matnr marc}werks

FROM MARA

INNER JOIN MARC

ON mara_{matnr = marc}matnr

WHERE mara~matnr = xxxxx.

joining 4 tables is similar.

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:38:48 GMT

Hi Akshitha

hope this will help you.

Pls reward if help.

Joins are used to fetch data fast from Database tables:

Tables are joined with the proper key fields to fetch the data properly.

If there are no proper key fields between tables don't use Joins;

Important thing is that don't USE JOINS FOR CLUSTER tableslike BSEG and KONV.

Only use for Transparenmt tables.

You can also use joins for the database VIews to fetch the data.

JOINS

... FROM tabref1 [INNER] JOIN tabref2 ON cond

Effect

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

Table 1 Table 2

|--|||--|

\ /

Inner Join

|--||||||||--|

Example

Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or a table alias.

Note

In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or WHEREclause. The following example returns the same solution as the previous one.

Example

Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID

WHERE F_{CONNID = P}CONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

Note

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

Each comparison in the ON condition must contain a field from the right-hand table.

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2

|--|||--|

\ /

Left Outer Join

NULL

|--||||||||--|

Example

Output a list of all custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

SBOOK_{FLDATE SBOOK}CARRID SBOOK_{CONNID SBOOK}BOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

Example

Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline.

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID )

INNER JOIN SCARR AS C

ON F_{CARRID = C}CARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:39:18 GMT

Example syntax as below:


select a~fld1 b~fld2 c~fld3 d~fld4...
       into <itab>
       from <tab1> as a
       inner join <tab2> as b
       on b~fld1 = a~fld1
       inner join <tab3> as c
       on c~fld2 = b~fld2
       inner join <tab4> as d
       on d~fld2 = b~fld2
       where <cond>.

Regards

Eswar

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:39:22 GMT

hi,

u can write inner join as many tables as u want if u have relational fields in the all the tables..

ex:select a_{fld1 b}fld2 c_{fld3 d}fld4...

into <itab>

from <tab1> as a

inner join <tab2> as b

on b_{fld1 = a}fld1

inner join <tab3> as c

on c_{fld2 = b}fld2

inner join <tab4> as d

on d_{fld2 = b}fld2

where <cond>.

reward points if useful,

seshu.

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:39:33 GMT

It is not advisable to use INNER JOIN on 4 tables. Use it on 3 Tables and use the 4th table in "For All Entries".

Check the code which will help you.

SELECT qals_{matnr qals}werk qals~mengeneinh

qals_{lmenge01 qals}lmenge02 qals~lmenge03

qals_{lmenge04 qals}lmenge05 qals~lmenge06

qals_{lmenge07 qals}lmenge08 qals~prueflos

qals_{art qals}charg qave~vcode

qave_{vdatum qmel}qmnum

INTO CORRESPONDING FIELDS OF TABLE i_qals

FROM qals

INNER JOIN qave

ON qals_{prueflos = qave}prueflos

INNER JOIN qmel

ON qals_{prueflos = qmel}prueflos

WHERE qals~matnr IN s_matnr

AND qals~werk IN s_werk

AND qals~art IN s_art

AND qals~stat34 EQ 'X'

AND qave~vcodegrp IN s_vcdgrp

AND qave~vcode IN s_vcode

AND qave~vdatum IN s_vdatum.

IF sy-subrc NE 0.

MESSAGE i000 WITH text-008.

ENDIF.

IF NOT i_qals IS INITIAL.

SELECT qmnum manum mncod INTO TABLE i_qmsm

FROM qmsm

FOR ALL ENTRIES IN i_qals

WHERE qmnum EQ i_qals-qmnum

AND mngrp LIKE 'Q_D%'

AND kzloesch NE 'X'.

ENDIF.

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:40:34 GMT

good

check this example

SELECT a~zbukr

a~hbkid

a~hktid

a~checf

a~waers

a~rwbtr

a~bancd

a~vblnr

a~pernr

a~voidr

a~rzawe

a~laufd

a~empfg

a~gjahr

a~zaldt

a~znme1

a~rec_belnr

b~bankl

c~bankn

d~duedt

FROM payr AS a

INNER JOIN t012 AS b

ON a_{hbkid = b}hbkid

inner join t012k as c

on a_{hbkid = c}hbkid

inner join t51r5 as d

on c_{bukrs = d}bukrs

into table it

WHERE a~zaldt IN s_zaldt

AND a~vblnr IN s_vblnr

AND a~znme1 IN s_znme1

AND a~rec_belnr IN s_rec

AND a~waers IN s_waers.

thanks

mrutyun^

Re: innerjoin

Former Member — Tue, 22 Jan 2008 07:42:34 GMT

TABLES: vbrk,vbrp,makt,kna1,t001.

SELECT-OPTIONS:

s_vkorg FOR vbrk-vkorg,

s_vbeln FOR vbrk-vbeln.

SELECT vbrk_{vbeln vbrk}fkdat vbrk_{zterm vbrk}inco1 vbrk~inco2

vbrk_{waerk vbrk}bukrs

vbrp_{matnr vbrp}fkimg vbrp_{vrkme vbrp}netwr

makt_{maktg kna1}name1 t001_{butxt kna1}adrnr

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( ( ( vbrk INNER JOIN vbrp ON vbrk_{vbeln = vbrp}vbeln )

INNER JOIN makt ON vbrp_{matnr = makt}matnr )

INNER JOIN kna1 ON vbrk_{kunag = kna1}kunnr )

INNER JOIN t001 ON vbrk_{bukrs = t001}bukrs )

WHERE vbrk~vkorg IN s_vkorg AND

vbrk~vbeln IN s_vbeln.

Thanks.

Also Refer this code :

SELECT a~kunnr

a~KVGR2

a ~ANGDT

a~BNDDT

a~BNAME

b~MATNR

c~PARNR

d~BZIRK

INTO CORRESPONDING FIELDS OF TABLE itab

FROM VBAK as a inner join

VBAP as b on a_{vbeln = b}vbeln

VBFA as c on a_vbeln = c~vbeln

KNVV as d on a_{vkotrg = d}VKORG

WHERE ................................

Reward points if helpful