LEFT OUTER JOIN and INNER JOIN in same select

[Former Member]

Hi,

I have 3 tables with following key field values (column is kalled id):

ztab1: one, two, three, four

ztab2: one, two, three

ztab3::one, two

I want to INNER JOIN table 2 and 3 and I want to LEFT OUTER JOIN table 1 and 2. So the result set I am expecting is values one, two, three, four.

This is my select that is not working but only returning one,two, three (four is missing).

SELECT ztab1~id
FROM ztab1 LEFT OUTER JOIN ztab2
      ON ztab1~id = ztab2~id INNER JOIN ztab3
      ON ztab2~id = ztab3~id

Why is this returning only one, two, three and not the four as well and how can I rewrite this select to that it works?

thanks

[Former Member]

The "ON" keyword compares the id -> FOUR from itab1 and it doesnt find any FOUR in itab 2.

[Former Member]

Hi,

SELECT ztab1~id

FROM ztab2 INNER JOIN ztab3

ON ztab2_{id = ztab3}id

LEFT OUTER JOIN ztab3

ON ztab1_{id = ztab2}id

Try the above...

Best Regards,

Suresh

[Former Member]

Unfortunately none of the above answers are correct.

I want to put up a more general question then. Say you have 3 tables where table 1 > 2 > 3

meaning 1 has more rows than 2, 2 have more rows than 3.

Now, left join table 1 and 2 and inner join table 2 and 3.

What would the resultset be if you wrote this in one single abap statement?

My guess is it should be all the rows from table 1 since the LEFT JOIN statements will disregard that there are no rows on the right hand side that matches.

My example unfortunately does not show this why i am wondering what I am missing.

[Former Member]

Hi Baran,

Using inner join for tab2 and tab3 is restricting with the less value table tab3.

Try to use left join twice, if your intention is set the priority to tab1.

Kind Regards,

Fernando Da Ró

[Former Member]

Hi,

Select a~fld1 a~fld2 a~fld3 a~fld4
from ztab1 as a left outer join ztab2 as b
on a~fld1 = b!fld1
and a~fld2=b~fld2
and a~fld3 = b~fld3
inner join ztab3 as c
on b~fld1 = c~fld1
and b~fld2 = c~fld3
into table itab.

[Former Member]

Hi,

Try the following which will work fine:

Select a_{field from ztab1 as a left outer join ztab2 as b on a}field = b~field

inner join ztab3 as c on b_{field = c}field into table itab.

best regards,

suresh