topic Re: joins in Application Development and Automation Discussions

joins

Former Member — Tue, 17 Jul 2007 09:26:06 GMT

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

if its possible send the eg query..

thanks & regards,

pandu

Re: joins

Former Member — Tue, 17 Jul 2007 09:29:51 GMT

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

Re: joins

former_member183924 — Tue, 17 Jul 2007 09:30:31 GMT

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

Re: joins

former_member196280 — Tue, 17 Jul 2007 09:31:03 GMT

Example.

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

INTO CORRESPONDING FIELDS OF TABLE T_OUTPUT

FROM QALS AS A INNER JOIN ZQMCOA AS B

ON A_{PRUEFLOS = B}PRUEFLOS

INNER JOIN VBAK AS C

ON C_{VBELN = A}LS_KDAUF

INNER JOIN TVAKT AS D

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.

Reward to all useful answers.

Regards,

Sairam

Re: joins

Former Member — Tue, 17 Jul 2007 09:33:36 GMT

Hello,

Here it is.

Please rewardif found helpfull.

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.

Re: joins

Former Member — Tue, 17 Jul 2007 09:33:57 GMT

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

Re: joins

Former Member — Tue, 17 Jul 2007 09:34:05 GMT

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.

Please reward points if useful

regards,

raksha

Re: joins

Former Member — Tue, 17 Jul 2007 09:36:58 GMT

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

Re: joins

Former Member — Tue, 17 Jul 2007 10:11:13 GMT

thank u to all