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

Dear experts,

I would like to find all the rows of table "A" that are not stored in table "B".

In Oracle I would use the Left Outer Join specifying "B.whtaeverfield is null" in the WHERE clause.

Unfortunately it seems that this is not possibile in ABAP ("No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE contintion ...).

Have you ever faced this situation? How did you manage it (without splitting the query into two sub-queries)?

Thanks a lot.

Alex

8 REPLIES 8

Former Member
0 Kudos

Look at subquery technique (see tips & tricks button in transaction SE30...SQL Interface select over more than one table )...for example:

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND NOT EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

With subquery, using the not exists would return only the first table rows that did not match. A limitation of subquery is that you cannot retain any of the data from the second select....

0 Kudos

... and using formatted code without use of alias AS F makes it even nicer:


SELECT * FROM SFLIGHT INTO SFLIGHT_WA
  WHERE SEATSOCC < SFLIGHT~SEATSMAX
AND NOT EXISTS ( SELECT * FROM SPFLI
WHERE CARRID = SFLIGHT~CARRID
  AND CONNID = SFLIGHT~CONNID
  AND CITYFROM = 'FRANKFURT'
  AND CITYTO = 'NEW YORK' )
  AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.

Regards

Clemens

Former Member
0 Kudos

Hi,

In ABAP joins can be used for 3 tables if there are more than 3 tables it is better to go with For All Entries, And according to your requiorement left outer join is possible in ABAP with a select statement to ignore un necessary records use where condition.

Regards,

Goutam Kolluru.

Former Member
0 Kudos

hiii,

you can try this below example code of left outer join query.....

DATA: CUSTOMER TYPE SCUSTOM,
      BOOKING  TYPE SBOOK.

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
       SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID
       INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
             BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
             BOOKING-BOOKID)
       FROM SCUSTOM LEFT OUTER JOIN SBOOK
         ON SCUSTOM~ID = SBOOK~CUSTOMID AND
            SBOOK~FLDATE = '20081015'
       ORDER BY SCUSTOM~NAME SBOOK~FLDATE.
  WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
           BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
           BOOKING-BOOKID.
ENDSELECT.

i hope from above example you vil get some idea.....

Former Member
0 Kudos

Hi,

Please check this link to get an idea about left outer join in ABAP.

http://help.sap.com/saphelp_470/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

Regards.

former_member185167
Active Contributor
0 Kudos

Hello,

I have the same question but unfortunately none of these replies provide the answer.

As the Original Poster says, we would like to get the non-matching rows in an outer join.

Sure, you can do it with a subquery but that could mean 10,000 selects instead of 1.

Yes, you can do it in ABAP itself but that would involve retrieving 99% useless data from the database.

Is it not possible in ABAP SQL?

We know what an outer join is, thanks.

regards

Rick

0 Kudos

Thanks Rick, for summing that up perfectly

Former Member

A little late to the party but in case you're still looking for an answer. First do an outer join so you get the complete set of data, including the records that have a match (the ones you don't want). Then exclude those in the where clause by means of an inner join:

data: begin of wa,

        carrid   type scarr-carrid,

        carrname type scarr-carrname,

        connid   type spfli-connid,

      end of wa,

      itab like sorted table of wa

  with non-unique key carrid.

select s~carrid, s~carrname, p~connid

into corresponding fields of table @itab

from scarr as s

left outer join spfli as p on s~carrid  = p~carrid and p~cityfrom = 'FRANKFURT'.

loop at itab into wa.

  write: / wa-carrid, wa-carrname, wa-connid.

endloop.

gives as result:

AA  American Airlines    0000

AB  Air Berlin           0000

AC  Air Canada           0000

AF  Air France           0000

AZ  Alitalia             0000

BA  British Airways      0000

CO  Continental Airlines 0000

DL  Delta Airlines       0000

FJ  Air Pacific          0000

JL  Japan Airlines       0408

LH  Lufthansa            0400

LH  Lufthansa            0402

LH  Lufthansa            2402

NG  Lauda Air            0000

NW  Northwest Airlines   0000

QF  Qantas Airways       0006

SA  South African Air.   0000

SQ  Singapore Airlines   0000

SR  Swiss                0000

UA  United Airlines      3517

UA  United Airlines      0941

select s~carrid, s~carrname, p~connid

into corresponding fields of table @itab

from scarr as s

inner join spfli as p on s~carrid  = p~carrid and p~cityfrom = 'FRANKFURT'.

gives as result:

JL  Japan Airlines       0408

LH  Lufthansa            2402

LH  Lufthansa            0402

LH  Lufthansa            0400

QF  Qantas Airways       0006

UA  United Airlines      3517

UA  United Airlines      0941

So if you exlude that set you get the following query:

select s~carrid, s~carrname, p~connid

into corresponding fields of table @itab

from scarr as s

left outer join spfli as p on s~carrid  = p~carrid and p~cityfrom = 'FRANKFURT'

           where s~carrid not in (  select s~carrid

                                     from scarr as s

                               inner join spfli as p on s~carrid  = p~carrid and p~cityfrom = 'FRANKFURT' ).

You end up with the result you're looking for, all entries that don't have a match:

AA  American Airlines    0000

AB  Air Berlin           0000

AC  Air Canada           0000

AF  Air France           0000

AZ  Alitalia             0000

BA  British Airways      0000

CO  Continental Airlines 0000

DL  Delta Airlines       0000

FJ  Air Pacific          0000

NG  Lauda Air            0000

NW  Northwest Airlines   0000

SA  South African Air.   0000

SQ  Singapore Airlines   0000

SR  Swiss                0000

Still a subquery but should be a lot more efficient since you only use 2 selects.