How to join the following 3 tables with exactly one ABAP SQL statement

Question

I have the following 3 tables with data:

ZMYTABLE with columns: ZUSER & ZTCODE and 2 records

elias  VA01  
elias  VF01

AGR_1251 with the records

SD_role  VA01  
SD2_role VA01  
SD3_role VA01  
SD_role  VA02  
FI_role  VF01  
FI_role  VF02

AGR_USERS with records

elias  SD_role  
elias  SD2_role   
maria  SD_role  
maria  FI_role

I want to display the fields ZUSER, ZTCODE and AGR_NAME.
I want all records from ZMYTABLE with the role in which exists each tcode for the specific user, namely:

ZUSER---ZTCODE---AGRNAME  
elias---VA01-----SD_role  
elias---VA01-----SD2_role  
elias---VF01-----

Can someone tell me how to do this by joining the 3 tables with exactly one ABAP SQL statement, in ABAP V7.01 sp07?

I cannot receive the result I want. It is returning only the common ones (1st 2 records). — ekekakos, Feb 07 '19 at 11:24
Question also asked on SCN : https://answers.sap.com/questions/750443/how-to-join-the-following-3-tables.html — Sandra Rossi, Feb 08 '19 at 09:24
Sandra, is there any problem to put a question in 2 forums? Thanks — ekekakos, Feb 08 '19 at 09:36
@ekekakos Not a problem at all. But people might find useful related answers, it avoids answering if solved in the other forum, some additional information may be given, etc. It's something I would personally do if I ask two questions occurring in the same context. — Sandra Rossi, Feb 08 '19 at 13:24

Sandra Rossi · Accepted Answer · 2019-02-08T11:05:58.497

I found it easier to use UNION, the first SELECT will return the lines which correspond to the transactions which match one of the user roles (elias, VA01, SD_role and SD2_role), and the second one will return the lines which correspond to the transactions which don't match any of the user roles (elias, VF01).

I tested it by replacing ZMYTABLE with USR07.

    SELECT usr07~bname, usr07~tcode, agr_1251~agr_name
      FROM agr_users
      INNER JOIN usr07
        ON usr07~bname EQ agr_users~uname
      INNER JOIN agr_1251
        ON agr_1251~agr_name EQ agr_users~agr_name
           AND agr_1251~low  EQ usr07~tcode
    UNION
    SELECT DISTINCT usr07~bname, usr07~tcode, ' ' AS agr_name
      FROM usr07
      WHERE NOT EXISTS (
        SELECT * FROM agr_users
          INNER JOIN agr_1251
            ON agr_1251~agr_name EQ agr_users~agr_name
          WHERE usr07~bname  EQ agr_users~uname
            AND agr_1251~low EQ usr07~tcode )
    INTO TABLE @DATA(result).

It gives these results (formatted for ABAP Unit) :

    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role'  )
      ( bname = 'elias' tcode = 'VF01' agr_name = ''         ) ) ).

Below is the ABAP Unit test code to demonstrate that it works, and you may play with it if needed. You need ABAP 7.52 (Open SQL Test Double Framework).

CLASS ltc_main DEFINITION FOR TESTING DURATION SHORT RISK LEVEL HARMLESS
      INHERITING FROM cl_aunit_assert.
  PRIVATE SECTION.
    METHODS test FOR TESTING.
    CLASS-METHODS: class_setup, class_teardown.
    CLASS-DATA environment TYPE REF TO if_osql_test_environment.
ENDCLASS.
CLASS ltc_main IMPLEMENTATION.
  METHOD class_setup.
    environment = cl_osql_test_environment=>create( i_dependency_list = VALUE #( 
          ( 'USR07' ) ( 'AGR_1251' ) ( 'AGR_USERS' ) ) ).
  ENDMETHOD.
  METHOD test.
    TYPES ty_usr07 TYPE STANDARD TABLE OF usr07 WITH EMPTY KEY.
    TYPES ty_agr_1251 TYPE STANDARD TABLE OF agr_1251 WITH EMPTY KEY.
    TYPES ty_agr_users TYPE STANDARD TABLE OF agr_users WITH EMPTY KEY.

    environment->insert_test_data( EXPORTING i_data = VALUE ty_usr07(
      ( bname = 'elias' tcode = 'VA01' timestamp = 1 )
      ( bname = 'elias' tcode = 'VF01' timestamp = 2 ) ) ).
    environment->insert_test_data( EXPORTING i_data = VALUE ty_agr_1251(
      ( agr_name = 'SD_role'  low = 'VA01' counter = 1 )
      ( agr_name = 'SD2_role' low = 'VA01' counter = 1 )
      ( agr_name = 'SD3_role' low = 'VA01' counter = 1 )
      ( agr_name = 'SD_role ' low = 'VA02' counter = 2 )
      ( agr_name = 'FI_role ' low = 'VF01' counter = 1 )
      ( agr_name = 'FI_role ' low = 'VF02' counter = 2 ) ) ).
    environment->insert_test_data( EXPORTING i_data = VALUE ty_agr_users(
      ( uname = 'elias' agr_name = 'SD_role ' )
      ( uname = 'elias' agr_name = 'SD2_role' )
      ( uname = 'maria' agr_name = 'SD_role ' )
      ( uname = 'maria' agr_name = 'FI_role ' ) ) ).

    "<==== here insert the ABAP SQL provided above & expectations to verify

    ROLLBACK WORK.

  ENDMETHOD.

  METHOD class_teardown.
    environment->destroy( ).
  ENDMETHOD.

ENDCLASS.

If you have an ABAP release < 7.50, UNION is impossible, instead define 2 separate SELECT, the first one with INTO TABLE @DATA(result) and the second one with APPENDING TABLE result.

PS: I also did the following tests, inspired from the other answers, they don't work (most of them return the role "FI_role" for "VF01", instead of an empty role).

Failed attempt 1-A:

     SELECT usr07~bname, usr07~tcode, agr_1251~agr_name
       FROM agr_users
       INNER JOIN usr07
         ON usr07~bname EQ agr_users~uname
       INNER JOIN agr_1251
         ON agr_1251~agr_name EQ agr_users~agr_name AND
            agr_1251~low      EQ usr07~tcode
       INTO TABLE @DATA(result).
    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' ) ) ).

Failed attempt 1-B:

     SELECT DISTINCT usr07~bname,
                 usr07~tcode,
                 agr_1251~agr_name
           FROM usr07
           INNER JOIN agr_1251
             ON agr_1251~low EQ usr07~tcode
           INNER JOIN agr_users
             ON agr_users~uname EQ usr07~bname
           WHERE
              agr_users~agr_name EQ agr_1251~agr_name OR EXISTS (
              SELECT *
              FROM agr_users AS inner_agr_users
              INNER JOIN agr_1251 AS inner_agr_1251
                ON inner_agr_1251~agr_name EQ inner_agr_users~agr_name
              WHERE
                inner_agr_users~agr_name EQ agr_1251~agr_name
           )
           INTO TABLE @DATA(result).
    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' )
      ( bname = 'elias' tcode = 'VF01' agr_name = 'FI_role' ) ) ).

Failed attempt 2:

     SELECT b~bname, b~tcode, a~agr_name
       FROM agr_1251 as a
       INNER JOIN usr07 as b
         ON a~low EQ b~tcode
       INNER JOIN agr_users as c
         ON a~agr_name EQ c~agr_name
       INTO TABLE @DATA(result).
    SORT result BY bname tcode agr_name.
    TYPES ty_result LIKE result.
    assert_equals( act = result exp = VALUE ty_result(
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD2_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' )
      ( bname = 'elias' tcode = 'VA01' agr_name = 'SD_role' )
      ( bname = 'elias' tcode = 'VF01' agr_name = 'FI_role' ) ) ).

Sandra, unfortunately, the system is an old one ECC6 V7.01 sp07, so there is no UNION. Anyway thanks. — ekekakos, Feb 08 '19 at 08:43
For these older systems, use 2 SELECT, the second one with `APPENDING TABLE` instead of `INTO TABLE`. — Sandra Rossi, Feb 08 '19 at 09:21
I am trying to solve this problem by using 2 tables, 1 with the common records (1st 2 in my example) and another 1 with all correct records in numbers but without being empty the appropriate AGR_NAME field. So I will loop into the 2nd table and I will read the 1st one with key all 3 fields. If sy-subrc <> 0 then I will empty the AGR_NAME field of the record. This is a BAD solution in my eyes, but there is no other direct solution until now. — ekekakos, Feb 08 '19 at 09:43
@ekekakos You are asking a question, and I give you an answer that I have tested and it gives the exact result you want (it's demonstrated by using ABAP Unit, 7.52 needed though, so I understand you can't execute it, but just trust me). I also explained how to replace `UNION` in your ABAP version. I don't understand why you say you still don't have a solution. — Sandra Rossi, Feb 08 '19 at 10:12
Sandra when say I DO NOT HAVE A SOLUTION I mean with a direct call to the DB with joins and not multiple calls in db with your solution or mine. I hope that I make myself clear. — ekekakos, Feb 08 '19 at 14:05
@ekekakos Now it's clear. Question updated to mention your requirement of only one SQL statement & ABAP 7.01 explicitly. — Sandra Rossi, Feb 08 '19 at 15:56

Dorad · Answer 2 · 2019-02-07T20:46:01.797

0

DB access should look like this:

SELECT DISTINCT zmytable~zuser,
                zmytable~ztcode,
                agr_1251~agr_name
FROM zmytable
INNER JOIN agr_1251
  ON agr_1251.ztcode EQ zmytable.ztcode
INNER JOIN agr_users
  ON agr_users.zuser EQ zmytable.zuser
WHERE
   agr_users.agr_name EQ agr_1251.agr_name OR EXISTS(
   SELECT *
   FROM agr_users AS inner_agr_users
   INNER JOIN agr_1251 AS inner_agr_1251
     ON inner_agr_1251.agr_name EQ inner_agr_users.agr_name
   WHERE
     inner_agr_users.agr_name EQ agr_1251.agr_name
).

Add INTO clause and deal with the output.

I am planning to upgrade the answer with explanations when I have time.

edited Feb 07 '19 at 20:46

answered Feb 07 '19 at 11:37

Dorad

3,413
2
44
71

This returns the common records namely the 1st 2 records in my example. Thanks – ekekakos Feb 07 '19 at 11:49
@ekekakos fixed – Dorad Feb 07 '19 at 20:38
For information, I have tested the query inside a test program (cf separate answer), it doesn't give the expected result. – Sandra Rossi Feb 07 '19 at 21:46
Dorad, unfortunately, it returns the correct number of records but the field AGR_NAME is not empty for the appropriate records. Thanks Elias – ekekakos Feb 08 '19 at 08:40

score 0 · Answer 3 · answered Feb 07 '19 at 13:03

0

To access all records, you should use DB Access as below.

SELECT b~zuser, b~ztcode, a~agr_name
  FROM agr_1251 as a
  INNER JOIN zmytable as b
    ON a~tcode EQ b~tcode
  INNER JOIN agr_user as c
    ON a~agr_name EQ c~agr_name.

Rest you can use ABAP to display output.

answered Feb 07 '19 at 13:03

Umar Abdullah

1,282
1
19
37

For information, I have tested the query inside a test program (cf separate answer), it doesn't give the expected result. – Sandra Rossi Feb 07 '19 at 21:46

Haojie · Answer 4 · 2019-02-08T16:01:49.500

0

Triple Join works for version > 7.4.

SELECT c~zuser, a~zrole, c~ztcode INTO CORRESPONDING FIELDS OF TABLE @lt_result
     FROM agr_users AS a INNER JOIN agr_1251 AS b 
         ON a~zrole = b~zrole
            RIGHT OUTER JOIN zmytable AS c 
              ON c~ztcode = b~ztcode AND c~zuser = a~zuser.

edited Feb 08 '19 at 16:01

answered Feb 08 '19 at 10:52

Haojie

5,665
1
15
14

Haojie, this is working in version > 7.50 not ours 7.01. Thanks. – ekekakos Feb 08 '19 at 14:11
OK. the answer is updated. It should also work in 7.01 – Haojie Feb 08 '19 at 14:41
`RIGHT OUTER JOIN` only works in ABAP >= 7.40. I have edited the question to mention it's for ABAP 7.01. – Sandra Rossi Feb 08 '19 at 16:00
@SandraRossi Thanks! I will check afterwards in 7.01. – Haojie Feb 08 '19 at 16:02

How to join the following 3 tables with exactly one ABAP SQL statement

4 Answers4