topic Re: LEFT OUTER JOIN and INNER JOIN in same select in Application Development and Automation Discussions

LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 09:28:36 GMT

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

Re: LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 09:30:55 GMT

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

Re: LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 09:33:38 GMT

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

Re: LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 10:08:07 GMT

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.

Re: LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 10:11:10 GMT

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.

Re: LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 10:17:59 GMT

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

Re: LEFT OUTER JOIN and INNER JOIN in same select

Former Member — Tue, 24 Mar 2009 10:28:11 GMT

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ó