topic INNER JOIN in Application Development and Automation Discussions

INNER JOIN

Former Member — Wed, 19 Jul 2006 15:34:32 GMT

guys!!! how can we join two fields each of three tables using INNER JOIN command?

like 2 fields each of MARA MARC MARD INTO one table using INNER JOIN command...

Regards,

ajit.

Re: INNER JOIN

RichHeilman — Wed, 19 Jul 2006 15:39:38 GMT

This can be done simply like this.




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.

Regards,

Rich Heilman

PS.

Put yourself on the SDN world map (http://sdn.idizaai.be/sdn_world/sdn_world.html) and earn 25 points.

Spread the wor(l)d!

Re: INNER JOIN

Former Member — Wed, 19 Jul 2006 15:40:02 GMT

SELECT a~matnr b~werks c~lgort
  into table itab from mara as a inner join marc as b inner join mard as c
    on a~matnr = b~matnr
    on b~matnr = c~matnr.

Re: INNER JOIN

Former Member — Wed, 19 Jul 2006 15:40:37 GMT


SELECT MARA~FIELD MARC~FIELD MARD~FIELD
INTO CORRESPONDING FIELDS OF TABLE
FROM MARA
 INNER JOIN MARC
 ON MARA~MATNR = MARC~MATNR
  AND MARA~XXX = MARC~XXX
 INNER JOIN ON MARD
 ON MARA~MATNR = MARD~MATNR
  AND MARA~YYY = MARD~YYY

Regards,

Ravi

Note : Please mark all the helpful answers

Re: INNER JOIN

Former Member — Thu, 20 Jul 2006 02:53:06 GMT

SORRY TO SAY!!! but none of your statements worked guyz!!!

Re: INNER JOIN

Former Member — Thu, 20 Jul 2006 03:06:44 GMT

Hi Ziden,

Go through the below sample code,

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

Reward points for helpful answers.

Regards,

Azaz Ali.

Re: INNER JOIN

hymavathi_oruganti — Thu, 20 Jul 2006 03:13:02 GMT

doesnot worked means what error u r getting.

all the stements above mentioned are correct.

what error u r facing and if u want some thing more, try to explain more clearly.

SELECT A_{MATNR A}MTART B_{WERKS B}LVORM C_{LGORT C}LABST

FROM MARA AS A INNER JOIN MARC AS B ON A_{MATNR = B}MATNR

INNER JOIN MARD AS C ON C_{WERKS = D}WERKS INTO CORRESPONDING FIELDS OF TABLE ITAB WHERE <CONDITION>.

Re: INNER JOIN

Former Member — Thu, 20 Jul 2006 03:14:39 GMT

hello rich!!!

Rn't u using aliaz names in your statement??

If using , can we use table name itself as aliaz names???

n moreover!!! dont we need to write

<table name> AS <aliaz name> in d statement

it wud be thankful if u clear all my doubts as u've written d statement.

and cud u plz use CORRESPONDING FIELDS OF in ya statement n write d stmt again...??

Regards,

ajit.

Re: INNER JOIN

hymavathi_oruganti — Thu, 20 Jul 2006 03:19:03 GMT

ALIAS STATEMENTS are not compulsory and also into corresponding fields is also not compulsory, if u retrive the fields in order.

what ever RICH written is excatly right. there would be no syntax error in that if u retrieve fields in order.

Re: INNER JOIN

Former Member — Thu, 20 Jul 2006 05:08:43 GMT

Rich Code is perfect....

Re: INNER JOIN

Former Member — Thu, 20 Jul 2006 05:14:17 GMT

select matnr into itab from

mara as a inner join marc as c

on a_{MATNR = c}MATnr

innerjoin MARD as d

on a_{MATNR = d}MATNR