Display Data Using Multiple Tables

[Former Member]

hi,

Display the Sales Order Details diplaying the range of Created Date and Sales Document type.

use SELECTION-SCREEN and SELECT-OPTIONS

4 fields to b displayed in output

Sales Order, Sales Document Type, Title and Names

Tables: KNA1, ADRC, VBAK

Thanks in Advance.

[Former Member]

HI,

use join....

c the SAPHELP docs...

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

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

A B C D D E F G H

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

a1 b1 c1 1 1 e1 f1 g1 h1

a2 b2 c2 1 3 e2 f2 g2 h2

a3 b3 c3 2 4 e3 f3 g3 h3

a4 b4 c4 3 -

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

\ /

\ /

\ /

\ /

\/

Inner Join

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

A B C D D E F G H

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

a1 b1 c1 1 1 e1 f1 g1 h1

a2 b2 c2 1 1 e1 f1 g1 h1

a4 b4 c4 3 3 e2 f2 g2 h2

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

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.

See the example for fetching data using joins from 3 tables

similarly you can use other tables which are linked with correct fields

select

a~vbeln " Billing Doc Number

a~fktyp " Billing Category

a~vbtyp " Sales Doc category

a~fkdat " Billing doc date

a~fkart " Billing doc type

a~bukrs " Company code

a~kurrf " Exchange rate

a~knumv " Condition record Number

a~waerk " Currency

a~kunag " Sold to Party

b~vrkme " Sales Unit

b~posnr " Item Number

b~charg " Batch Number

b~fkimg " Billed quantity

b~werks " Plant

b~matnr " Material Number

b~netwr " Net Value of Bill Doc

b~wavwr " Cost in Doc Currency

c~kdmat " Customer Material

into table itab_bill

from vbrk as a join vbrp as b

on b_{vbeln = a}vbeln

join vbap as c

on b_{aubel = c}vbeln and

b_{aupos = c}posnr

where a~vbeln in s_vbeln and

a~fkdat in s_fkdat and

a~bukrs in s_bukrs and

a~vtweg in s_vtweg and

a~vkorg in s_vkorg and

a~spart in s_spart and

a~fkart in s_fkart and

b~werks in s_werks and

a~kunag in s_kunag and

a~sfakn eq ' ' and

a~fksto eq ' ' .

Reward Points if found helpfull..

Cheers,

Chandra Sekhar.

[Former Member]

Hi Satish,

You can select the data for more than on tables through Joins.

See' demo_select_inner_join' Program in SE38 tcode.

Also, check this sample code.

Try out with the following code.

SELECT a~matnr

a~berid

a~dismm

a~ausss

a~sobsl

b~berty

INTO TABLE it_mdma_mdlv_mdll

FROM mdma AS a

INNER JOIN mdlv AS b

ON a_{berid EQ b}berid

INNER JOIN mdll AS c

ON b_{berid EQ c}berid

FOR ALL ENTRIES IN it_bomitems

WHERE a~matnr EQ it_bomitems-idnrk

AND a~dispo IN s_cdispo

AND b~berty EQ '3'

AND a~sobsl IN s_csobsl

AND c~lbear IN s_lifnr.

this query contains join for 3 different tables.

kindly reward if found helpful.

cheers,

Hema.