I have two tables with the same key. I want to, in one SELECT, without subsequent manipulation of data in an internal table, retrieve all records in the left-hand table where there is NO CORRESPONDING record in the right-hand table (i.e. columns from the right-hand table would be empty).
The most logical thing to do would be the following, but this does not compile, because you may not use a field from the right-hand side in an outer join in the WHERE clause:
select e~equnr into lt_equnr
from equi as e
left outer join eqbs as b on e~equnr = b~equnr
where e~matnr = material
and b~b_werk = space.
An alternative that looks promising and compiles is this, but it does not work, as it brings back even those that have corresponding entries in the right-hand table:
select e~equnr into table lt_equnr
from equi as e
left outer join eqbs as b on e~equnr = b~equnr
and b~b_werk = space
where e~matnr = material.
This option merely blanks out fields from the right-hand side, but still includes everything in the result set. This can be confirmed by selecting fields from the right-hand side.
Another option, which also does not work, is using a sub-select:
select e~equnr into table lt_equnr
from equi as e
where e~matnr = material
and e~equnr not in ( select equnr from equi where equnr = e~equnr ).
As pointed out in the comments on the question, there was a bug in my code. In my sub-select I was using the LHS table. (My sub-select was referencing EQUI instead of EQBS).
By fixing my sub-select thus, it works:
select e~equnr into table lt_equnr
up to max_entries rows
from equi as e
where e~matnr = material
and e~equnr not in ( select equnr from eqbs where equnr = e~equnr ).