topic left join between two tables in Application Development and Automation Discussions

left join between two tables

Former Member — Fri, 24 Jul 2020 13:23:45 GMT

to get only the data from the left table I have to use a right left join?

this is the code:

select *

from marc

left outer join mseg on marc ~ matnr = mseg ~ matnr and marc ~ werks = mseg ~ werks

into CORRESPONDING FIELDS OF TABLE it_marc

WHERE marc ~ WERKS in s_plant and mseg ~ CPUTM_MKPF> = sy-uzeit. .

gives me this error:

If new Open SQL syntax is used, all host variables must be escaped using @. The variable S_PLANT is not escaped.

Re: left join between two tables

former_member1716 — Fri, 24 Jul 2020 13:50:27 GMT

Hello nick99999,

You just have to use inner join which is left inner join by default. Error is due to missing @ symbol on all variables used which is the principle while using open SQL statements. Below code for your reference,

select *
from marc
Inner join mseg 
on marc ~ matnr = mseg ~ matnr and marc ~ werks = mseg ~ werks
into CORRESPONDING FIELDS OF TABLE @it_marc
WHERE marc ~ WERKS in @s_plant and mseg ~ CPUTM_MKPF> = @sy-uzeit. .

Regards!

Re: left join between two tables

Former Member — Fri, 24 Jul 2020 13:53:43 GMT

does this just take the data from the marc table?

Re: left join between two tables

Sandra_Rossi — Fri, 24 Jul 2020 15:13:50 GMT

nick99999 because of the presence of CORRESPONDING FIELDS, it depends on what component names are declared in IT_MARC. If you have one component with the same name as one column from MSEG, then it will read this column. But if you don't have any component with the same name as a column from MSEG, then MSEG won't be read.

(and that means of course that you must use component names with the names of columns from MARC to read data from MARC)

Re: left join between two tables

Former Member — Fri, 24 Jul 2020 15:30:21 GMT

sandra.rossi

the it_marc table is the same as the marc table. I'm only interested in the values of the marc table, because for example using the filters in se16 in the marc there is only one corresponding row (and I want my internal table to have only that row) while in the mseg there are 36 rows. in the select it brings me all 36 rows when I just want one of the marc table

Re: left join between two tables

Sandra_Rossi — Fri, 24 Jul 2020 15:43:47 GMT

What you're looking for doesn't imply a JOIN, it implies EXISTS:

DATA dummy_plant TYPE marc-werks.
SELECT-OPTIONS s_plant FOR dummy_plant.
DATA it_marc TYPE TABLE OF marc.
SELECT * FROM marc
  WHERE marc~werks IN @s_plant
    AND EXISTS (
      SELECT * FROM mseg
      WHERE marc~matnr = mseg~matnr
        AND marc~werks = mseg~werks
        AND mseg~cputm_mkpf >= @sy-uzeit )
 INTO TABLE @it_marc.

By the way, it seems obvious to me that you should test both CPUTM_MKPF and CPUDT_MKPF:

AND ( mseg~CPUDT_MKPF > @sy-datum
   OR ( mseg~CPUDT_MKPF = @sy-datum
    AND mseg~cputm_mkpf >= @sy-uzeit ) )

NB: I removed CORRESPONDING FIELDS because the target data object has the same structure as the database table.