topic Re: ABAP SQL join syntax in Application Development and Automation Discussions

ABAP SQL join syntax

Former Member — Wed, 27 Jul 2005 20:30:46 GMT

Hi,

I am wondering if anyone can provide me with examples on correct syntax for different join types such as inner join, left outer join, right outer join, outer full join, and cross join.

Thank you in advance,

Sunny

Re: ABAP SQL join syntax

RichHeilman — Wed, 27 Jul 2005 20:36:30 GMT

In ABAP, there are only INNER JOINs and LEFT OUTER JOINs.




report zrich_0004.


data: begin of itab occurs 0,
      matnr type mara-matnr,
      maktx type makt-maktx,
      end of itab.



      select * into corresponding fields of table itab
              from mara
                   inner join makt
                      on mara~matnr = makt~matnr
                            where mara~mtart = 'HALB'
                              and makt~spras = sy-langu.



       select * into corresponding fields of table itab
              from mara
                   left outer join makt
                      on mara~matnr = makt~matnr
                            where mara~mtart = 'HALB'.

Regards,

Rich HEilman

Re: ABAP SQL join syntax

Former Member — Thu, 28 Jul 2005 03:38:27 GMT

Note this sample code for inner join using 4 tables.

This can be done by using Inner join in select query u can do a f1 check in select query,

eg.

* SELECT c~ekgrp
* a~matnr
* b~maktx
* c~werks
* c~minbe
* INTO CORRESPONDING FIELDS OF TABLE i_output
* FROM mara AS a INNER JOIN makt AS b
* ON a~matnr = b~matnr
* INNER JOIN marc AS c
* ON a~matnr = c~matnr
* inner join MSKU as d* on a~matnr = d~matnr* WHERE a~matnr IN s_matnr
* AND a~mtart IN s_mtart
* AND c~werks IN s_werks
* AND c~ekgrp IN s_ekgrp
* AND c~lgfsb IN s_lgfsb
* and d~KUNNR in s_kunnr
* and d~sobkz = c_w
* and d~kulab > 0.

Similarly u can check the sample code in the documentation.

Thanks & Regards,

Judith.

Re: ABAP SQL join syntax

jayanthi_jayaraman — Thu, 28 Jul 2005 04:05:54 GMT

Hi,

Check this for left outer join.

SELECT KNA1_{KUNNR KNA1}ADRNR ADR6~SMTP_ADDR

INTO (A, B, C)

FROM KNA1 LEFT OUTER JOIN ADR6

ON KNA1_{ADRNR = ADR6}ADDRNUMBER

WHERE KUNNR BETWEEN '0000000000' AND '0000500000'.

Check this for inner join.

select db1_{field1 db2}field2 from db1 inner join db2 into table itab on db1_{field = db2}field.