regarding inner join

[Former Member]

hey!!

can anybody tell me how to use iner join with select statement with common fields in the two and other common fields with another two tables.Give an suitable example.

regards,

[Former Member]

See example:

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.

Regards

[former_member386202]

Hi,

Refer this code for inner join

*--Select query to pick the Billing document Billing date Billing

• category Billing type Distribution channel Number of doc condition

• Billing item Material Number Plant Referance Subtotal 2 Cost in

• document currency and Partner function from table VBRK VBRP and VBPA

• using inner join

SELECT a~vbeln "Billing document

a~fkdat "Billing date

a~fktyp "Billing category

a~fkart "Billing type

a~vtweg "Distribution channel

a~knumv "Number of doc condition

b~posnr "Billing item

b~matnr "Material Number

b~werks "Plant

b~vgbel "Referance

b~kzwi2 "Subtotal 2

b~wavwr "Cost in document currency

c~kunnr "Partner function

FROM vbrk AS a

INNER JOIN vbrp AS b

ON a_{vbeln EQ b}vbeln

INNER JOIN vbpa AS c

ON b_{vbeln EQ c}vbeln

INTO CORRESPONDING FIELDS OF TABLE it_vbrp

WHERE fkdat IN s_erdat

AND parvw EQ 'ZS'.

IF sy-subrc EQ 0.

*--Sort table by Referance

SORT it_vbrp BY vgbel1.

ENDIF.

but it reduces the performance so better to use FOR ALL ENTRIES

*--Select query to pick the Delivery Material Number Plant

• Movement type Subtotal 2 and Quantity from table LIPS

SELECT vbeln "Delivery

matnr "Material Number

werks "Plant

bwart "Movement type

kzwi2 "Subtotal 2

lfimg "Quantity

FROM lips

INTO CORRESPONDING FIELDS OF TABLE it_lips

FOR ALL ENTRIES IN it_vbrp

WHERE vbeln EQ it_vbrp-vgbel1.

IF sy-subrc EQ 0.

*--Sort table by Delivery

SORT it_lips BY vbeln.

ENDIF.

Regards,

Prashant

[Former Member]

refer following querry for inner join on more than two tables...

select a~bukrs

a~anln1

a~anln2

a~anlkl

a~txt50

a~ord41

a~ord42

a~ord43

b~afabe

b~ndabj

b~ndabp

b~kansw

b~knafa

b~kaafa

b~nafag

b~nafap

b~aafam

b~aafag

c~kostl

d~afasl

d~ndjar

d~ndper

into corresponding fields of table gt_master

from ( ( anla as a inner join anlc as b

on a_{bukrs = b}bukrs

and a_{anln1 = b}anln1

and a_{anln2 = b}anln2 )

inner join anlz as c

on a_{bukrs = c}bukrs

and a_{anln1 = c}anln1

and a_{anln2 = c}anln2 )

inner join anlb as d

on a_{bukrs = d}bukrs

and a_{anln1 = d}anln1

and a_{anln2 = d}anln2

where a~bukrs in s_comp

and a~anlkl in s_astcls.