topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Mon, 28 Aug 2006 12:05:21 GMT

hi friends,

help me to write inner join on two tables.

regards

siri.

Re: Inner join

Former Member — Mon, 28 Aug 2006 12:06:23 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,

ravi

Re: Inner join

Former Member — Mon, 28 Aug 2006 12:09:13 GMT

ex--

data: begin of itab occurs 0,

matnr type mara-matnr,

mtart type mara-mtart,

werks type marc-werks,

dispo type marc-dispo,

lgort type mard-lgort,

labst type mard-labst,

end of itab.

select mara~matnr mara~mtart marc~werks marc~dispo
       mard~lgort mard~labst
              into table itab
                      from mara
                          inner join marc
                             on mara~matnr = marc~matnr
                          inner join mard
                             on marc~matnr = mard~matnr
                            and marc~werks = mard~werks
                                  up to 10 rows.

Re: Inner join

Former Member — Mon, 28 Aug 2006 12:09:31 GMT

Hi,

Go thro' the link, this might help http://help.sap.com/saphelp_47x200/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

Rgds,

Re: Inner join

Former Member — Mon, 28 Aug 2006 12:12:16 GMT

hi,

check this example.

REPORT ZSK005.

TABLES : LIKP.

DATA : BEGIN OF I_LIKP_LIPS OCCURS 0,

VBELN LIKE LIKP-VBELN,

LFART LIKE LIKP-LFART,

LFDAT LIKE LIKP-LFDAT,

VGBEL LIKE LIPS-VGBEL,

VGPOS LIKE LIPS-VGPOS,

END OF I_LIKP_LIPS.

SELECT-OPTIONS : S_VBELN FOR LIKP-VBELN OBLIGATORY MATCHCODE OBJECT VMVA

START-OF-SELECTION.

SELECT LIKP_{VBELN LIKP}LFART LIKP_{LFDAT LIPS}VGBEL LIPS~VGPOS

INTO TABLE I_LIKP_LIPS

FROM LIKP INNER JOIN LIPS

ON LIKP_{VBELN = LIPS}VBELN

WHERE LIKP~VBELN IN S_VBELN.

IF SY-SUBRC <> 0.

MESSAGE E001(ZX).

ENDIF.

END-OF-SELECTION.

LOOP AT I_LIKP_LIPS.

WRITE : / I_LIKP_LIPS-VBELN,I_LIKP_LIPS-LFART,I_LIKP_LIPS-LFDAT,

I_LIKP_LIPS-VGBEL,I_LIKP_LIPS-VGPOS.

ENDLOOP.

Re: Inner join

Former Member — Mon, 28 Aug 2006 12:14:50 GMT

Hi Sireesha,

Check this,

data : begin of itab occurs 0,

matnr like mara-matnr,

ersda like mara-ersda,

maktx like makt-maktx,

end of itab.

SELECT A_{matnr A}ersda b~maktx

INTO itab

FROM mara AS A INNER JOIN makt AS b

ON A_{matnr = b}matnr

ENDSELECT.