topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Tue, 24 Apr 2007 05:33:34 GMT

hi,

can u give me for joining three tables with inner join into one table

regards,

praveen

Re: inner join

Former Member — Tue, 24 Apr 2007 05:36:27 GMT

Hi Praveen,

check this,

select ekko_{ebeln ekko}bsart ekko~bedat

ekko_{ekorg ekko}ekgrp ekko~waers

ekpo_{ebelp ekpo}matnr ekpo~menge

ekpo_{meins ekpo}netpr ekpo~netwr

into corresponding fields of table i_ekko

from ekko inner join ekpo on ekko_{ebeln = ekpo}ebeln

where ekko~ebeln in s_ebeln and

ekko~waers = 'USD'.

if sy-subrc <> 0.

message e003.

endif.

thanks,

shreya

Re: inner join

Former Member — Tue, 24 Apr 2007 05:36:39 GMT

Hi,

see this

data: begin of itab_bill occurs 0,

vbeln like vbrk-vbeln, " Billing Document Number

fktyp like vbrk-fktyp, " Billing Category

vbtyp like vbrk-vbtyp, " Sales Doc Category

fkdat like vbrk-fkdat, " Billing doc date

fkart like vbrk-fkart, " Billing Doc type

bukrs like vbrk-bukrs, " Company Code

kurrf like vbrk-kurrf, " Exchange type

knumv like vbrk-knumv, " Condition Number

waerk like vbrk-waerk, " Currency

kunag like vbrk-kunag, " Sold to Party

vrkme like vbrp-vrkme, " sales Unit

posnr like vbrp-posnr, " Item Number

charg like vbrp-charg, " Batch Number

fkimg like vbrp-fkimg, " Billed quantity

werks like vbrp-werks, " Plant

matnr like vbrp-matnr, " Material Number

netwr like vbrp-netwr, " Net Value of the doc

wavwr like vbrp-wavwr, " Cost in Doc Currency

kdmat like vbap-kdmat, " Customer Material

end of itab_bill.

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 useful

regards,

Anji

Re: inner join

satykumar — Tue, 24 Apr 2007 05:36:41 GMT

check this might be helpful for you

Re: inner join

Former Member — Tue, 24 Apr 2007 05:37:01 GMT

Hi Praveen,

let the tables be a,b,c.

select a_{authlname a}authlnam a_{authfname a}authfnam a_{isbn b}title

c~<fieldname>

from bsauthors as a inner join bsbook as b on a_{isbn = b}isbn

inner join <dbtab> as c on a_{<fieldname> = <C}FIELDNAME>

into corresponding fields of table books

where a_{authlname like authorlname or a}authfname like authorfname

order by a_{authlname a}authfname a~isbn.

Regards,

Priyanka.

Re: inner join

Former Member — Tue, 24 Apr 2007 05:37:12 GMT

Hi,

HAVE A LOOK .......

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

Reward all helpful answers

Regards,

V.Raghavender.

Re: inner join

Former Member — Tue, 24 Apr 2007 05:37:48 GMT

refer the code below.

SELECT A~MBLNR

B~BUDAT

A~MATNR

A~MENGE

C~EXTWG

D~EMPCD

D~EMP_NAME

A~DMBTR

D~GCNNO

D~REASON

INTO TABLE T_MSEG

FROM MSEG AS A INNER JOIN MKPF AS B

ON A_{MBLNR = B}MBLNR

LEFT JOIN ZDEMO AS D

ON A_{MBLNR = D}MBLNR

INNER JOIN MARA AS C

ON A_{MATNR = C}MATNR

WHERE A~BUKRS IN S_BUKRS

AND A~WERKS IN S_WERKS

AND D~EMPCD IN S_EMPCD

AND C~EXTWG IN S_EXTWG

AND B~BUDAT IN S_BUDAT

AND A~MBLNR = '4900000865'

I think it will solve ur problem.

Rewards with point if helpful.

Regards

Santosh.

Re: inner join

Former Member — Tue, 24 Apr 2007 05:38:36 GMT

Hi Praveen,

This is the Sample Code for Inner Join Using Three Tables.

tables: zvijirank,

zvijirank1,

zvijirank2. .

*data : a type zvijirank occurs 10 with header line,

b type zvijirank1 occurs 10 with header line,
C TYPE ZVIJIRANK2 OCCURS 10 WITH HEADER LINE.

data : name like zvijirank-name,

total like zvijirank1-total,

reg_no like zvijirank,

F_NAME LIKE ZVIJIRANK2-F_NAME.

regno type i.

data : a type zvijirank occurs 10 with header line,

b type zvijirank1 occurs 10 with header line,

C TYPE ZVIJIRANK2 OCCURS 10 WITH HEADER LINE.

data : begin of wa,

name type zvijirank-name,

total type zvijirank1-total,

branch type zvijirank1-branch,

reg_no type zvijirank-reg_no,

F_NAME TYPE ZVIJIRANK2-F_NAME,

M_NAME TYPE ZVIJIRANK2-M_NAME,

CITY TYPE ZVIJIRANK2-CITY,

TEL_NO TYPE ZVIJIRANK2-TEL_NO,

end of wa.

data : it like standard table of wa with header line,

it1 like standard table of wa with header line.

*selection-screen : begin of screen 9010.

*parameters : regno TYPE ZVIJIRANK-REG_NO matchcode object zreg.

*PARAMETERS : NAME1(20) TYPE C MATCHCODE OBJECT ZREG1.

*selection-screen : end of screen 9010.

*call selection-screen 9010.

*select a_{name b}total b_{branch a}reg_no into table it from zvijirank as a inner join zvijirank1 as b on a_{reg_no = b}reg_no .

SELECT-OPTIONS REGNO FOR ZVIJIRANK-REG_NO MATCHCODE OBJECT ZREG.

SELECT

A~NAME

A~REG_NO

B~TOTAL

B~BRANCH

C~F_NAME

C~M_NAME

C~CITY

C~TEL_NO

INTO CORRESPONDING FIELDS OF TABLE IT

FROM ( ( ZVIJIRANK AS A INNER JOIN ZVIJIRANK1 AS B ON A_{REG_NO = B}REG_NO ) INNER JOIN ZVIJIRANK2 AS C ON C_{REG_NO = B}REG_NO ) WHERE ( B~REG_NO IN REGNO ) .

loop at it.

*if it-reg_no eq regno.

*IF IT-NAME EQ NAME.

write : / 'NAME :', it-name,

/ 'REG_NO :', it-reg_no,

/ 'TOTAL :', it-total,

/'FATHER NAME :', IT-F_NAME,

/ 'MOTHER NAME :', IT-M_NAME,

/ 'CITY :', IT-CITY,

/ 'TELPHONE NO :', IT-TEL_NO.

ULINE.

*ENDIF.

endloop.

Re: inner join

Former Member — Tue, 24 Apr 2007 05:38:55 GMT

Hi,

select spfli~carrid " Airline Code

sbook~connid " Flight Connection Number

sbook~fldate " Flight date

sbook~wunit " Weight Unit

scustom~id " Customer Number

scustom~name " Customer name

into corresponding fields of table t_flight

from spfli as spfli join

sbook as sbook join

scustom as scustom on

sbook_{customid eq scustom}id

where sbook~carrid eq p_carrid

and sbook~connid eq p_connid.

regards,

kiran kumar k

Re: inner join

Former Member — Tue, 24 Apr 2007 05:38:59 GMT

Hi,

select spfli~carrid

spfli~connid

sflight~fldate

sflight~price

sbook~bookid

into corresponding fields of table t_table

from (( spfli as spfli join sflight as sflight

on spfli_{carrid eq sflight}carrid

and spfli_{connid eq sfight}connid ) join sbook as sbook

on sflight_{fldate eq sbook}fldate ).

Regards,

Re: inner join

Former Member — Tue, 24 Apr 2007 05:40:56 GMT

Hai Praveen Here is complete Program having inner join using 3 Tables:

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Hope you can do it now.

Reward points if it helps you.

Regds,

Rama chary.Pammi