To fetch Data from multiple database tables!

[Former Member]

How to fetch Data from fields of multiple database tables!

Give me one example!

[former_member624107]

use <b>join....</b>

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.

[Former Member]

Hi,

You have the select statement to do this.

If two tables are relarted.

Get the entires one table and use for all entries of first table. And get the data from the second using for all entries of first table or else you can also use Inner Joins for this.

Please let us know what are the tables you want to use?

Thanks,

Sriram Ponna.

[Former Member]

Hi

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 ' ' .

Regards

anji

[VikasB]

Hi,

Take a look on this Select Statement.

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.

Regards,

vikas.

plz reward if helpful

[Former Member]

Hi Jyotirmoy,

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

See' demo_select_inner_join' Program in SE38 tcode.

Plz Reward if useful,

Mahi.