topic Re: joinig two tables in select statement in Application Development and Automation Discussions

joinig two tables in select statement

Former Member — Sat, 10 Feb 2007 16:35:22 GMT

Hi all,

I want some ex code for how to join two tables in using for statement in select query and also please give me ex code for more than two tables join in select query.

Re: joinig two tables in select statement

Former Member — Sat, 10 Feb 2007 16:43:23 GMT

Hi Pavan,

For two tables:

Types: begin of tma,

matnr type mara-matnr,

mtart type mara-mtart,

werks type mard-werks,

lgort type mard-lgort,

labst type mard-labst,

end of tma.

data: ima type table of tma with header line.

select mara_{matnr mara}mtart mard_{werks mard}lgort mard~labst

into table ima from mara

inner join mard on mara_{matnr = mard}matnr

up to 10 rows.

LOOP at ima.

WRITE:/ ima-MATNR, ima-WERKS, ima-mtart, ima-lgort, ima-labst.

ENDLOOP.

Check this code for 3 tables:

DATA : BEGIN OF itab OCCURS 0,

matnr LIKE mara-matnr,

ersda LIKE mara-ersda,

werks LIKE mard-werks,

pstat LIKE mard-pstat,

ekgrp LIKE marc-ekgrp,

ausme LIKE marc-ausme,

END OF itab.

SELECT : mara~matnr

mara~ersda

mard~werks

mard~pstat

marc~ekgrp

marc~ausme

INTO TABLE itab UP TO 20 ROWS

FROM mara

INNER JOIN mard

ON mara_{matnr = mard}matnr

INNER JOIN marc

ON mara_{matnr = marc}matnr.

IF sy-subrc = 0.

LOOP AT itab.

WRITE 😕 itab-matnr,

itab-ersda,

itab-werks,

itab-pstat,

itab-ekgrp,

itab-ausme.

ENDLOOP.

ENDIF.

Regards,

Beejal

**Reward if this helps

Re: joinig two tables in select statement

Clemenss — Sat, 10 Feb 2007 19:35:26 GMT

Hi pavan,

excellent examples given.

Note:

The number of resulting lines is the product of matching lines: If there is no matching entry in any of the joined tables, no record is fetched.

If the ON condition matches more than one record in all joined tables, resulting lines give all possible combinations (e.g. MVKE joined on MATNR with MAKT will result sentences number of sales areas multiplied by number of languages maintained).

The join condition is similar to the WHERE condition, it can take expressions combined using logical expressions. Conditions may be specified in ON or in WHERE Clause.

AND: This is the (default) INNER JOIN. Use (less performant) LEFT OUTER JOIN to fetch initial values when no matching record exists.

Regards,

Clemens

P.S.: All this can be found in ONLINE DOCUMENTATION. SAP examples are not too useful using confusing alias with AS clause.

Re: joinig two tables in select statement

Former Member — Sat, 10 Feb 2007 19:59:37 GMT

good

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.

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

thanks

mrutyun^

Re: joinig two tables in select statement

Former Member — Sun, 11 Feb 2007 14:33:37 GMT

hi pavan,

Hope this piece of code helps u.

Report zjoin_arun1.

tables : kna1,knb1, knbk.

data : begin of itab1 occurs 0,

kunnr like kna1-kunnr,

land1 like kna1-land1,

name1 like kna1-name1,

ort01 like kna1-ort01,

regio like kna1-regio,

bukrs like knb1-bukrs,

pernr like knb1-pernr,

end of itab1.

select-options : cust for kna1-kunnr.

start-of-selection.

select a_{kunnr a}land1 a_{name1 a}ort01 a_{regio b}bukrs b~pernr into

corresponding fields of table itab1 from kna1 as a inner join knb1 as b

on a_{kunnr eq b}kunnr where a~kunnr in cust.

Loop at itab1.

Write : / itab1-kunnr, itab1-land1,itab1-name1,

itab1-ort01,itab1-regio, itab1-bukrs,itab1-pernr.

Endloop.

Regards...

Arun.

Reward if it helps u.

Re: joinig two tables in select statement

Former Member — Mon, 12 Feb 2007 06:51:47 GMT

Hi,

SELECT A_{EQUNR B}DATAB B_{ILOAN C}SWERK

INTO TABLE ITEQ

FROM ( EQUI AS A INNER JOIN EQUZ AS B

ON A_{EQUNR = B}EQUNR ) INNER JOIN ILOA AS C

ON B_{ILOAN = C}ILOAN

WHERE A~EQART = 'ESTPL'

AND B~IWERK = 'M011'

AND B~INGRP = 'SLM'

AND B_{DATAB GE SDATE-LOW AND B}DATAB LE SDATE-HIGH

AND C~SWERK IN S_WERK.

This select query is for more than 2 tables & if you want it for 2 tables remove the portion in bold.

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: joinig two tables in select statement

p291102 — Mon, 12 Feb 2007 07:15:31 GMT

Hi,

This is the sample select query for joining 3 tables.select a_{ematn a}ebeln a_{ebelp a}menge a_{meins a}TXZ01 a~elikz

b~PS_PSP_PNR

c_{ekgrp c}bedat c_{lifnr c}ekorg

d_{posid e}matnr g_{stprs "f}lifnr f_{mblnr f}mjahr f_{erfmg f}erfme

from ekpo as a inner join ekkn as b

on a_{ebeln eq b}ebeln and a_{ebelp eq b}ebelp

inner join ekko as c on a_{ebeln eq c}ebeln

inner join prps as d on b_{ps_psp_pnr eq d}pspnr

inner join mdsb as e

on a_{ebeln eq e}ebeln and a_{ebelp eq e}ebelp

inner join mbew as g on g_{matnr eq e}matnr

inner join mseg as f on f_{matnr eq e}matnr

into corresponding fields of table itab where

d~posid in wbs and

c~ekgrp in pgroup and

c~ekorg in purorg and

a~ebeln in purdocno and

c~bedat in pdate and

c~lifnr in vendor .

Thanks,

Shankar

Re: joinig two tables in select statement

Former Member — Mon, 12 Feb 2007 09:48:15 GMT

Please give me one ex code using for all also to join two tables in select query.