topic Re: table joins in Application Development and Automation Discussions

table joins

Former Member — Wed, 29 Nov 2006 13:48:33 GMT

hi,

Kindly tell me how to wrute this query ...

I have a field 'f1' in a table T1 and a similar field 'f2' in table T2. I need to find the list of the fields f1 from table T1 which are not present in T2.

I have written a query .... please revert back with modifications needed ...

select a~f1

into var

from T1 as a

WHERE not exists ( select b~f2 from tT2 as b

where a_{f1 = b}f2

and b~firma eq 'SW01'

and b~object eq 'ACCOUNT' ).

write : / var.

endselect.

thx..

paul

Re: table joins

Former Member — Wed, 29 Nov 2006 13:58:15 GMT

Hi Pradipta,

The following code will work for you.


select  a~f1
   into  table t_var
   from T1 as a
   join T2 as b
   on   a~T1 NE b~T2
  where b~firma eq 'SW01'
    and b~object eq 'ACCOUNT').

loop at t_var into wa_var.
write: wa_var.
endloop.

Regards

Bhupal Reddy

Message was edited by:

Bhupal Reddy Vendidandi

Re: table joins

Former Member — Wed, 29 Nov 2006 14:01:26 GMT

Hi Paul,

This isn't a join but a subquery.

Take a look at this example from SAP Help on ( Select --> Subquery )


Selecting all flights from Frankfurt to New York between 1.1.1999 and 31.3.1999 that are not yet full: 



DATA: WA_SFLIGHT TYPE SFLIGHT. 

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT 
    WHERE SEATSOCC < F~SEATSMAX 
      AND 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'. 
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, 
           WA_SFLIGHT-FLDATE. 
ENDSELECT. 



Subqueries such as the one in this example,
in which the WHERE clause uses fields from the main
query, are known as correlated subqueries. 
Subqueries can be nested, and a given subquery may contain any fields from other,
 hierarchically-superior subqueries.

Hope this helps,

Erwan

Message was edited by:

Erwan LE BRUN

Re: table joins

andreas_mann3 — Wed, 29 Nov 2006 14:04:08 GMT

hi,

here's a sample:

SELECT * FROM ANIA AS A INTO TABLE XANIA WHERE NOT EXISTS

       ( SELECT *  FROM ANIB WHERE OBJNR = A~OBJNR AND

                                   LFDNR = A~LFDNR ).