topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Tue, 31 Jan 2006 09:40:12 GMT

plse explain how to write inner join

thanks with regards aaryaa

Re: inner join

Former Member — Tue, 31 Jan 2006 09:44:33 GMT

Hi Aaryaa,

Please see below example to write inner join.


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 AND 
              F~CONNID = P~CONNID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '19970910' AND '19970920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / DATE, CARRID, CONNID. 
ENDSELECT.

Selects data from the transparent database tables or views specified in tabref1 and tabref2. tabref1 and tabref2 either have the same form as in variant 1 or are themseleves joine expressions. The key word INNER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized in the ABAP/4-Dictionary and declared in the program with an appropriate TABLES statement.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help standardization (see relational database). To regroup this information in a database query, you can link tables using a join command . This formulates conditions for the columns of the tables involved. An inner join contains all combinations of lines from database table tabref1 with lines from database table tabref2 that meet the condition specified in the logical condition ON cond.

Thanks&Regards,

Siri.

Re: inner join

hymavathi_oruganti — Tue, 31 Jan 2006 09:44:56 GMT

selecr a_{matnr b}maktx from mara as A ineer join MAKT

AS B ON A_{MATNR = B}MATNR INTO TABLE ITAB.

Re: inner join

Former Member — Tue, 31 Jan 2006 09:51:24 GMT

Hi,

did you check the Help on Join's.

if not check it.

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by the ABAP Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

Table 1 Table 2

|--|||--|

\ /

Inner Join

|--||||||||--|

| A | B | C | D | D | E | F | G | H |

|--||||||||--|

| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |

| a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 |

| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 |

|--||||||||--|

DATA: BEGIN OF WA, 
        FLIGHT TYPE SFLIGHT, 
        PFLI   TYPE SPFLI, 
        CARR   TYPE SCARR, 
      END OF WA.

SELECT * INTO WA 
    FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P 
             ON F~CARRID = P~CARRID AND 
                F~CONNID = P~CONNID ) 
           INNER JOIN SCARR AS C 
             ON F~CARRID = C~CARRID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '20010910' AND '20010920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID, 
           WA-FLIGHT-CONNID. 
ENDSELECT.

Re: inner join

Former Member — Tue, 31 Jan 2006 10:22:43 GMT

Hi,

Point to remember is that you can join cluster

tables example cdhdr,cdpos.

Regards

Amole

Re: inner join

Former Member — Tue, 31 Jan 2006 10:41:13 GMT

Hi,

here is a sample of how to perform multiple table joins..

into an itab.

 
  SELECT VBAP~ERDAT VBAK~VBELN VBAK~VBELN VBAK~VTWEG VBAK~SPART
VBAP~POSNR
 VBAP~MATNR
 LIKP~VBELN LIKP~LFART LIKP~LFDAT LIPS~VGBEL  LIPS~LFIMG FROM ( ( ( VBAK
 INNER JOIN VBAP ON  VBAP~VBELN = VBAK~VBELN ) INNER JOIN LIPS ON
LIPS~VGBEL = VBAP~VBELN ) INNER JOIN LIKP ON LIKP~VBELN = LIPS~VBELN )
INTO TABLE I_LIKPLINK WHERE VBAK~VBELN IN S_VBEL AND LIPS~VGPOS =
VBAP~POSNR.
  ULINE.



  endform.

Re: inner join

Former Member — Tue, 31 Jan 2006 10:44:30 GMT

Hi Aaryaa,

Mainly you need to concentrate on the key fields while using Join conditions. If you do not find data in any of your joined tables you will not be able to fetch that particular record.

Here is the sample code for shipment details.

select vttp_{tknum vttp}tpnum vttp_{vbeln vepo}posnr

vepo_{venum vepo}vepos vekp_{exidv vepo}vemng vbfa~vbelv

vbfa_{posnv vbap}matnr vttk_{tdlnr vttk}route

vttk_{erdat vttk}exti1

into table i_shipment

from vttp

inner join vttk

on vttk_{tknum = vttp}tknum

inner join vepo

on vepo_{vbeln = vttp}vbeln

inner join vekp

on vekp_{venum = vepo}venum

inner join vbfa

on vbfa_{vbeln = vepo}vbeln

and vbfa_{posnn = vepo}posnr

inner join vbap

on vbap_{vbeln = vbfa}vbelv

and vbap_{posnr = vbfa}posnv

where vttp~tknum = v_shipment.

Regards,

Sudheer

Re: inner join

Former Member — Tue, 31 Jan 2006 10:44:31 GMT

Hi,

Small correction to my earlier mail.

Inner join is not possible on cluster table.

Regards

Amole

Re: inner join

Former Member — Tue, 31 Jan 2006 10:50:40 GMT

Example :

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

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.

Re: inner join

Former Member — Tue, 31 Jan 2006 10:56:54 GMT

HI Madhu

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

Chk out this link......for further detials....

http://help.sap.com/saphelp_erp2005/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

Cheers:-)

Mithlesh

Re: inner join

Former Member — Tue, 31 Jan 2006 11:04:55 GMT

Hi,

Proper use of Inner Join:

=========================

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry

into table int_airdet

From zairln as a inner join zflight as b on

a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

I hope this info is suffice.

Regs,

Venkat Ramanan