How to join the following 3 tables

[ekekakos]

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? Is there any way?

The system is an old one ECC6 V7.01 sp07.

Thanks

Elias

[Sandra_Rossi]

Question also asked on stackoverflow : https://stackoverflow.com/questions/54572190/how-to-join-the-following-3-tables

[former_member186746]

Hi,

You can go to transaction ABAPDOCU and read the stuff on selects and joins.

But what is it functionally what you're trying to achieve? A list of users and their transactions in their authorization roles?

There's probably an SAP standard report or transaction with which you can conjure up this information.

Kind regards, Rob Dielemans

[ekekakos]

No Rob it's not a standard table. We have a custom one and we must check this custom table with the standards. Unfortunately, there is no RIGHT JOIN in SAP. I found a workaround and solve my problem, but this is not a good program code. Of course if you do not have the full functionality of the JOIN, you go to the bad code.

Elias

[ArthurParisius]

Your saying SAP doesn't have a RIGHT JOINm if I check ABAPDOCU I do find the RIGHT OUTER JOIN, which I think should do what you want.

[ekekakos]

Arthur, in which version? In ours no

SELECT - join

Syntax

... [(] {dbtab_left [AS tabalias_left]} | join
{[INNER] JOIN}|{LEFT [OUTER] JOIN}
{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

[former_member182550]

Isn't an outer join equivalent to a Right Join ?

[former_member591577]

If there is a strong need to go to database (DB) only once (which is implicitly the best practice), then I would go for selecting the abundance of data and then doing sort and deletion of internal table. Otherwise the work around could be 2 selects: firstly, selecting data from ZMYTABLE only and then doing SELECT FROM agr_users JOIN agr_1251 FOR ALL ENTRIES IN (previously selected ZMYTABLE). Anyhow, one select and deletion could propably go as this:

SELECT ZUSER, ZTCODE, AGR_1251~AGR_NAME, AGR_USERS~(ZUSER equivalent)
	INTO TABLE lt_user_specific_tcodes_by_role
	FROM ZMYTABLE
	INNER JOIN AGR_1251 ON ZMYTABLE~ZTCODE = AGR_1251~? (ZTCODE equivalent)
	INNER JOIN AGR_USERS ON ZMYTABLE~ZUSER  = AGR_USERS-? (ZUSER equivalent) 

DELETE lt_user_specific_tcodes_by_role WHERE (ZUSER equivalent) = ''.

The idea behind this is to also select information which line is abundant (AGR_USERS~(ZUSER equivalent) eg. you wont fill username to your internal table if there is none in AGR_USERS) and then removing it. Sorry if the code is not precise - I cant access any system right now. Though I hope that this idea can get you started. But personally, as a novice programmer, I would go for 2 selects and for all entries solution - althought it puts more load on DB, I find it easier to understand.