joins

[Former Member]

can any one tell me , how to join three tables using inner join?

if its possible send the eg query..

thanks & regards,

pandu

[Former Member]

can any one tell me , how to join three tables using inner join?

if its possible send the eg query..

thanks & regards,

pandu

[Former Member]

Refer following:

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

[former_member183924]

Hi pandu,

it should be not so hard.

SELECT *
FROM  ( ekko INNER JOIN  ekpo  ON ekko~ebeln = ekpo~ebeln
             INNER JOIN  eket  ON eket~ebeln = ekpo~ebeln AND eket~ebelp = ekpo~ebelp )
INTO CORRESPONDING FIELDS OF TABLE it_lab_data
WHERE ekko~lifnr IN so_lifnr
AND   ekpo~werks IN so_werks
AND   ekpo~matnr IS NOT NULL
...

[former_member196280]

Example.

SELECT A_{LS_KDAUF B}LS_VBELN C_{AUART D}BEZEI

INTO CORRESPONDING FIELDS OF TABLE T_OUTPUT

FROM <b>QALS AS A INNER JOIN ZQMCOA AS B</b>

ON A_{PRUEFLOS = B}PRUEFLOS

<b> INNER JOIN VBAK AS C</b>

ON C_{VBELN = A}LS_KDAUF

<b> INNER JOIN TVAKT AS D</b>

ON D_{AUART = C}AUART

WHERE B~WERKS EQ P_WERKS

AND B~ERSTELDAT IN S_CREDAT

AND B~PRUEFLOS IN S_INSLOT

AND B~ZPRUEFLOS IN S_PRELOT.

<b>Reward to all useful answers.</b>

Regards,

Sairam

[Former Member]

Hello,

Here it is.

<b>Please rewardif found helpfull.

</b>

Regards,

Rakesh.

*Join details: lips = a
*              likp = b
*              kna1 = c

  SELECT c~name1 a~vgbel a~vgpos a~mtart c~name2
         b~vkorg a~ntgew a~gewei a~werks
         b~inco1 b~inco2

         INTO TABLE it_del
         FROM ( ( lips AS a

         INNER JOIN likp AS b ON a~vbeln = b~vbeln )
         INNER JOIN kna1 AS c ON c~kunnr = b~kunnr )

         WHERE b~lfdat IN so_date
         AND   b~vstel IN so_shppt
         AND   a~matnr IN so_matnr.

[Former Member]

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN. Depending on the type of join, a join expression can be either an inner (INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

Example :

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. 

Girish

[Former Member]

chk the code below

SELECT

a~objectclas

a~objectid

a~tcode

a~CHANGENR

a~udate

a~change_ind

b~fname

b~tabname

b~value_new

b~value_old

c~maktx

d~aktnr

e~aktkt

INTO CORRESPONDING FIELDS OF TABLE gt_crf

FROM ( ( ( ( cdhdr AS a

INNER JOIN cdpos AS b ON a_{objectclas = b}objectclas

AND a_{objectid = b}objectid

AND a_{changenr = b}changenr )

INNER JOIN zak_pbbp AS d ON d_{matnr = a}objectid )

LEFT OUTER JOIN makt AS c ON c_{matnr = a}objectid )

LEFT OUTER JOIN wakt AS e ON e_{aktnr = d}aktnr )

WHERE a~objectclas = l_objectclas

AND b~tabname = l_tabname

AND b~fname = l_fname

AND a~tcode = l_tcode

AND a~change_ind = l_change_ind

AND b_{value_new NE b}value_old

AND a~udate GT ls_reports-ldate.

<b>Please reward points if useful</b>

regards,

raksha

[Former Member]

Hi

Try like this

 TYPES:
BEGIN OF TY_OUTPUT,
KUNNR TYPE VBAK-KUNNR,
VBELN TYPE VBAK-VBELN,
AUDAT TYPE VBAK-AUDAT,
VBTYP TYPE VBAK-VBTYP,
MATNR TYPE VBAP-MATNR,
ZMENG TYPE VBAP-ZMENG,
NETPR TYPE VBAP-NETPR,
NETWR TYPE VBAP-NETWR,
MAKTX TYPE MAKT-MAKTX,
END OF TY_OUTPUT.
 
DATA:
ST_OUTPUT TYPE TY_OUTPUT,
IT_OUTPUT TYPE TY_OUTPUT.
 
SELECT-OPTIONS: SKUNNR FOR ST_OUTPUT-KUNNR.
SELECT-OPTIONS: SAUDAT FOR ST_OUTPUT-AUDAT.
SELECT-OPTIONS:  SVBTYP FOR ST_OUTPUT-VBTYP.
 
SELECT VBAK~KUNNR VBAK~VBELN VBAK~AUDAT  VBAK~VBTYP VBAP~MATNR VBAP~ZMENG VBAP~NETPR VBAP~NETWR MAKT~MAKTX INTO TABLE IT_OUTPUT
FROM VBAK 
INNER JOIN VBAP ON VBAK~VBELN = VBAP~VBELN 
WHERE VBAK~KUNNR = SKUNNR
             VBAK~AUDAT = SAUDAT 
              VBAK~VBTYP = SVBTYP.



Check this sample

Select single Vbrk~Bukrs Vbrk~Kunrg    Vbrk~Vbeln  
                   Vbrk~Fkdat Vbrk~Bstnk_Vf Vbrk~Zterm  
                   Tvzbt~Vtext  
                   Vbak~Vbeln Vbak~Bstdk  
                   Likp~Vbeln Likp~lfdat    Likp~Lfuhr  
       into w_vbrk  
       from vbrk  
      inner join       Tvzbt on Tvzbt~Zterm        = Vbrk~Zterm      and  
                                Tvzbt~Spras        = sy-langu  
      Inner join       Vbfa  as SalesLnk  
                             on SalesLnk~vbeln     = pu_vbeln        and  
                                SalesLnk~vbtyp_v   = c_order  
            inner join Vbak  on Vbak~Vbeln           = SalesLnk~Vbelv 
      Inner join       Vbfa  as DeliveryLnk  
                             on DeliveryLnk~vbeln   = pu_vbeln       and  
                                DeliveryLnk~vbtyp_v = c_Delivery  
            inner join Likp  on Likp~Vbeln          = DeliveryLnk~Vbelv  
      where vbrk~vbeln = pu_Vbeln.  

Reward all helpfull answers

Regards

Pavan

[Former Member]

thank u to all