topic Re: Outer join, use in a query. in Application Development and Automation Discussions

Outer join, use in a query.

Former Member — Sun, 29 Oct 2006 17:40:07 GMT

Hi forum!

I want to do a query among three tables keeping empties rows. Is this possible to do with an outer join?

Could anybody give me an example how to use it¿?

Thanks in advance and regards,

Mon

Re: Outer join, use in a query.

Former Member — Sun, 29 Oct 2006 18:03:23 GMT

When joining more than one table, you can use one outer join in the select statement, so if your missing rows are in one table, you can do the outer join on that table.

Rob

Re: Outer join, use in a query.

Former Member — Sun, 29 Oct 2006 20:22:49 GMT

Hi again.

Is it possible to do a query outer join among three tables in the same query¿?

Any suggestion or example¿?

Mon

Re: Outer join, use in a query.

Former Member — Mon, 30 Oct 2006 03:39:07 GMT

hi Mon,

Check

http://help.sap.com/saphelp_erp2005vp/helpdata/en/e2/5a12fede7c11d5994e00508b5d5211/frameset.htm

http://help.sap.com/saphelp_erp2005vp/helpdata/en/67/7e4b3eaf72561ee10000000a114084/frameset.htm

http://help.sap.com/saphelp_erp2005vp/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

Yes we can do that ..

Check

Regards,

Santosh

Message was edited by: Santosh Kumar Patha

Re: Outer join, use in a query.

Former Member — Mon, 30 Oct 2006 04:23:44 GMT

Yes you can use outer join for the same.

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition <cond>. <b>The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.</b>

SELECT ...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

For details and examples of outer join please have a look at below links.

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

http://database.ittoolbox.com/documents/popular-q-and-a/inner-and-outer-join-sql-statements-2108

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers