left outer join

[Former Member]

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

[Former Member]

Hi,

You can restrict the record in WHERE clause.

Regards,

Atish

[Former Member]

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 mara_{matnr mara}mtart marc_{werks marc}dispo

into table imatnr

from mara

<b> left outer join marc</b>

on mara_{matnr = marc}matnr

up to 10 rows.

SELECT MARA~MATNR

MARA~MTART

MARC~WERKS

INTO TABLE ITAB

FROM MARA LEFT OUTER JOIN MARC

ON MARA_{MATNR EQ MARC}MATNR

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]

you should pay more attention to

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

[Former Member]

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.