topic Re: inner join condition in Application Development and Automation Discussions

inner join condition

Former Member — Thu, 13 Nov 2008 06:45:01 GMT

actuall i knew how to club the two database tables using innerjoin.

ther conditios is

select--

fields----- into itab from (dtab) inner join (dtab)

on condition

where so and so---

the thing is that i need to add another table data(database table).what is the code and syntax.

please reply with example in detail.thanku.

Re: inner join condition

former_member386202 — Thu, 13 Nov 2008 06:47:31 GMT

Hi,

Better to use FOR ALL ENTRIES, dont use inner join.

refer below code

&----

*& Form sub_read_mara *

&----

This form will Select the material number material type material *
group & batch management indicator from table mara using given *
material number *

----

FORM sub_read_mara .

*--Select query to pick the material number material group material type

and batch management from table MARA

SELECT matnr "Material Number

mtart "Material Type

matkl "Material Group

xchpf "Batch Management

FROM mara "Material Master

INTO TABLE i_mara

WHERE matnr IN s_matnr

AND mtart IN s_mtart

AND matkl IN s_matkl .

*--Check Subrc

IF sy-subrc <> 0.

*--No data found for the given selection criteria.

MESSAGE i001.

LEAVE TO LIST-PROCESSING.

ELSE.

*--Sort table by Material Number

SORT i_mara BY matnr.

ENDIF.

ENDFORM. "sub_read_mara

&----

*& Form sub_read_marc *

&----

This form will Select the material number plant purchasing group & *
MRP controller from table MARC using given material number plant & *
purchasing group. *

----

FORM sub_read_marc .

IF NOT i_mara[] IS INITIAL.

*--Select query to pick the material number plant purchasing group &

MRP controller from table MARC

SELECT matnr "Material Number

werks "Plant

ekgrp "Purchasing Group

dispo "MRP Controller

FROM marc "Material Master

INTO TABLE i_marc

FOR ALL ENTRIES IN i_mara

WHERE matnr = i_mara-matnr

AND werks IN s_werks

AND ekgrp IN s_ekgrp

AND dispo IN s_dispo.

*--Check Subrc

IF sy-subrc <> 0.

MESSAGE i001.

LEAVE TO LIST-PROCESSING.

ELSE.

*--Sort table by Material Number Plant

SORT i_marc BY matnr werks.

ENDIF.

ENDFORM. "sub_read_marc

Regards,

Prashant

Re: inner join condition

kesavadas_thekkillath — Thu, 13 Nov 2008 06:49:35 GMT

Check inner join Docs ...

select Mara_{matnr marc}werks makt_{maktx T001w}name1 into table itab

from mara inner join marc

on mara_{matnr = marc}matnr

inner join T001w on marc_{werks = T001w}werks

inner join mard on mard_{matnr = marc}matnr

where mara~mtart = 'FERT'

and marc~werks = '100'

and makt~spras = sy-langu...

some thing like this.......

Re: inner join condition

Former Member — Thu, 13 Nov 2008 06:50:37 GMT

Hi,

Hope the following Threads will help you regarding your problem(Just go through this post)

check the code below

SELECT ekko~ebeln

ekpo_{ebelp ekko}lifnr

ekbe_{zekkn ekbe}gjahr ekbe_{belnr ekbe}buzei

lfa1~name1

lfb1~ernam

ekkn_{sakto ekkn}gsber ekkn~kostl

INTO TABLE po_int

FROM ekko

JOIN ekpo ON ekpo_{ebeln = ekko}ebeln

JOIN lfa1 ON lfa1_{lifnr = ekko}lifnr

JOIN ekbe ON ekbe_{ebeln = ekpo}ebeln

AND ekbe_{ebelp = ekpo}ebelp

JOIN lfb1 ON lfb1_{lifnr = ekko}lifnr

JOIN ekkn ON ekkn_{ebeln = ekpo}ebeln

AND ekkn_{ebelp = ekpo}ebelp

WHERE ekko~aedat IN s_aedat

AND ekbe~bwart = '101'

AND lfb1~bukrs = p_bukrs.

Thanks.

Nitesh

Edited by: Nitesh Kumar on Nov 13, 2008 12:21 PM

Re: inner join condition

Former Member — Thu, 13 Nov 2008 06:50:47 GMT

SELECT a~pabnum

a~werks " Plant

a~lifnr " Supplier

a~pabprf " JIT call profile

a~pabnpl " Output partner for JIT call

a~pabzpk " Time for a JIT call

a~pabdkz " Message: JIT call edited

a~ekorg " Purchasing organization

b~pabpos " JIT call item

b~matnr " Material number

b~pabmng " JIT call quantity

b~meins " Base unit of measure

b~ebeln " Agreement number

b~ebelp " Agreement item

b~ablad " Unloading point

b~prvbe " Supply area

b~pabtim " Time for a JIT call

b~pabwef " Goods receipt flag for JIT call

b~pabwem " Goods receipt qty for JIT call

INTO TABLE itab_jit_call

FROM pabhd AS a

INNER JOIN pabit AS b

ON b_{pabnum EQ a}pabnum

WHERE a~werks IN s_werks

AND a~lifnr IN s_lifnr

AND a~pabdkz IN s_pabdkz

AND b~matnr IN s_matnr

AND b~prvbe IN s_prvbe

AND b~pabwef IN s_pabwef

AND b~ablad IN s_uablad.

IF sy-subrc EQ 0.

Re: inner join condition

Former Member — Thu, 13 Nov 2008 06:51:10 GMT

Example : As you have asked for

SELECT single vbap~matkl into V3

from ( ( VBAP inner join VBFA

ON vbap-vbeln = vbfa-vbelp )

inner join LIPS

on LIPS_{POSNR = VBFA}POSNN

and vbap_{vbeln = vbfa}vbelv

and vbap_{posnr = vbfa}posnv )

where LIPS_{VBELN = VBFA}VBELN = '0080000834'

and vbfa~vbtyp_n = 'J'

and LIPS~J_3ASIZE = '0884'.

Re: inner join condition

Former Member — Thu, 13 Nov 2008 07:19:17 GMT

hi,

check this:


*&---------------------------------------------------------------------*
*& Report  ZDAVID_SELECT
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZDAVID_SELECT.
tables: zdavid_emp, zdavid_dep,zdavid_detail.

types: begin of info,
  zemp_id(10) type c,
  zemp_dep(10) type c,
  zemp_dname(10) type c,
  end of info.

types: emp_info type table of info.

data: emp type emp_info,
      wa like line of emp.

start-of-selection.
  select *  into corresponding fields of wa
    from zdavid_emp as a inner join zdavid_dep as b
    on a~zemp_id = b~zemp_id
    inner join zdavid_detail as c
    on b~zemp_dep = c~zemp_dep.
    write: / wa-zemp_id, wa-zemp_dep, wa-zemp_dname.
    endselect.