topic Re: regarding inner join in Application Development and Automation Discussions

regarding inner join

Former Member — Wed, 28 Nov 2007 05:58:28 GMT

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,

Re: regarding inner join

Former Member — Wed, 28 Nov 2007 06:01:08 GMT

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

Re: regarding inner join

former_member386202 — Wed, 28 Nov 2007 06:01:49 GMT

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

Re: regarding inner join

Former Member — Wed, 28 Nov 2007 06:01:56 GMT

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.