Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

left outer join

Former Member
0 Kudos
290

How to omit records from the left table in a left outer join?

4 REPLIES 4

Former Member
0 Kudos
89

Hi,

You can restrict the record in WHERE clause.

Regards,

Atish

Former Member
0 Kudos
89

Here is an example of an outer join. Here is there is no match in MARC, it will still retrieve the MARA data.

data: begin of imatnr occurs 0,

matnr type mara-matnr,

mtart type mara-mtart,

werks type marc-werks,

dispo type marc-dispo,

end of imatnr.

select maramatnr maramtart marcwerks marcdispo

into table imatnr

from mara

<b> left outer join marc</b>

on maramatnr = marcmatnr

up to 10 rows.

SELECT MARA~MATNR

MARA~MTART

MARC~WERKS

INTO TABLE ITAB

FROM MARA LEFT OUTER JOIN MARC

ON MARAMATNR EQ MARCMATNR

WHERE MARA~MATNR IN S_MATNR.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

e.g.

INNER JOIN: Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something

SELECT Customers., Orders.

FROM Customers INNER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

-


LEFT OUTER JOIN: Retrieves all customers with or without orders. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN if you switch the side of each table.

SELECT Customers., Orders.

FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

-


RIGHT OUTER JOIN: Retrieves all orders with or without matching customer records. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers., Orders.

FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Check below SAP help.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

Rewards if useful.........

Minal

Former Member
0 Kudos
89

you should pay more attention to

where cause can not refer to the fields which include in the left join table.

Former Member
0 Kudos
89

you can try inner join

when you use left join

you should pay more attention to

where cause can not refer to the fields which include in the left join table.