SELECT WITH JOINS
Select with Joins
1. It is used to bring the data simultaneously
from multiple tables.
2. That means the multiple tables must be joined
using one or two field.
3. There should be at least one common field between
tables.
4. We have 2 types of joins.
1. INNER JOIN
1. Inner Join is used to bring comparable
(matching) records between tables.
2. Unmatched Records won’t be selected.
2. OUTER JOIN
1. It joins all the records from the first table
or left table will be selected first.
2. If there are any comparable (matching) records
from second, third tables, then the data will be displayed.
3. And if there are no matching records, the data
will be displayed as blank from second and third tables.
SYNTAX
Select Table1~Field1
Table1~Field2
Table2~Field1 Table2~Field2
Table3~Field1 Table1~Field2
Into Table <ITAB>
From Table1
<INNER JOIN/OUTER JOIN>
Table2 On Table1~Field1 = Table2~Field1
<INNER JOIN/OUTER JOIN> Table3 On
Table2~Field1 = Table3~Field1
Where <Condition>.
EXAMPLE PROGRAM ON INNER JOIN
TYPES:
BEGIN OF TY_VBAK_VBAP,
VBELN TYPE VBAK-VBELN,
ERDAT TYPE VBAK-ERDAT,
ERZET TYPE VBAK-ERZET,
ERNAM TYPE VBAK-ERNAM, "STRUCTURE THAT HOLDS THE FIELDS OF 2 TABLES
POSNR TYPE VBAP-POSNR,
MATNR TYPE VBAP-MATNR,
END OF TY_VBAK_VBAP.
DATA:
IT_VBAK_VBAP TYPE TABLE OF TY_VBAK_VBAP, "ITAB AND WA DECLARATION
WA_VBAK_VBAP TYPE TY_VBAK_VBAP.
SELECT VBAK~VBELN VBAK~ERDAT
VBAK~ERZET VBAK~ERNAM
VBAP~POSNR VBAP~MATNR "FETCHING DATA WITH INNER JOIN ON VBELN
INTO TABLE
IT_VBAK_VBAP
FROM VBAK
INNER JOIN VBAP ON
VBAK~VBELN = VBAP~VBELN.
LOOP AT IT_VBAK_VBAP INTO WA_VBAK_VBAP.
WRITE:/ WA_VBAK_VBAP-VBELN COLOR 3,WA_VBAK_VBAP-ERDAT,
WA_VBAK_VBAP-ERZET, "OUTPUT DISPLAY
WA_VBAK_VBAP-ERNAM,
WA_VBAK_VBAP-POSNR,
WA_VBAK_VBAP-MATNR.
ENDLOOP.
BEGIN OF TY_VBAK_VBAP,
VBELN TYPE VBAK-VBELN,
ERDAT TYPE VBAK-ERDAT,
ERZET TYPE VBAK-ERZET,
ERNAM TYPE VBAK-ERNAM, "STRUCTURE THAT HOLDS THE FIELDS OF 2 TABLES
POSNR TYPE VBAP-POSNR,
MATNR TYPE VBAP-MATNR,
END OF TY_VBAK_VBAP.
DATA:
IT_VBAK_VBAP TYPE TABLE OF TY_VBAK_VBAP, "ITAB AND WA DECLARATION
WA_VBAK_VBAP TYPE TY_VBAK_VBAP.
SELECT VBAK~VBELN VBAK~ERDAT
VBAK~ERZET VBAK~ERNAM
VBAP~POSNR VBAP~MATNR "FETCHING DATA WITH INNER JOIN ON VBELN
INTO TABLE
IT_VBAK_VBAP
FROM VBAK
INNER JOIN VBAP ON
VBAK~VBELN = VBAP~VBELN.
LOOP AT IT_VBAK_VBAP INTO WA_VBAK_VBAP.
WRITE:/ WA_VBAK_VBAP-VBELN COLOR 3,WA_VBAK_VBAP-ERDAT,
WA_VBAK_VBAP-ERZET, "OUTPUT DISPLAY
WA_VBAK_VBAP-ERNAM,
WA_VBAK_VBAP-POSNR,
WA_VBAK_VBAP-MATNR.
ENDLOOP.
OUTPUT:
