topic Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Mon, 22 Nov 2010 14:50:50 GMT

Hi All,

I have recently came across this statement.

select t001w_{werks t001w}ekorg t001w~vlfkz

from ( t001w inner join t024w on t024w_{werks = t001w}werks )

into table lt_t001w

where t001w~vlfkz in lr_vlfkz and

t001w~ekorg in lr_ekorg.

Basically in T024W, I have 25 entries of WERKS of different EKORG and in T001W, i have 13 entries of WERKS of EKORG = 1001 and VLFKZ = A. When running the statement above with the condition 1001 in LR_EKORG and A in LR_VLFKZ repectively, I get all 25 entries with those WERKS which is not originally EKORG = 1001 in T024W becomes 1001 in the output result. I couldn't put my fingers together for this...anyone can help me here?

Thanks!

Moderator message: please use more descriptive subject lines when starting a thread.

Edited by: Thomas Zloch on Nov 22, 2010 4:33 PM

Re: Inner Join

Former Member — Mon, 22 Nov 2010 15:13:19 GMT

Try with the below code...

select a_{werks b}ekorg b~vlfkz

from t024w as a inner join t001w as b

on a_{werks = b}werks

into table it_t001w

where b~vlfkz in lr_vlfkz and

b~ekorg in lr_ekorg.

Regards

Praveen

Re: Inner Join

brad_bohn — Mon, 22 Nov 2010 15:39:33 GMT

Well, that other post isn't really going to help - adding an alias and changing the field selection for one field...

To answer your question, think about the restriction you have put on the selection and the join: the join is by PLANT and the restriction is by purchasing org from T001W (only). You did not restrict the purchasing org selection from T024W. This means that the join will pick up all purchasing org entries for the plants that were found in T001W. Look at a trace and you'll see how it works. It sounds like since you received all 25 entries from T024W, then the plants found in the T001W cover all of those entries?

Re: Inner Join

tushar_shukla — Mon, 22 Nov 2010 19:48:21 GMT

Cady,

Brad has already pointed out the root cause . You can add EKORG in where clause while joining the two tables e.g.

select t001w_{werks t001w}ekorg t001w~vlfkz

from ( t001w inner join t024w on t024w_{werks = t001w}werks AND t024w_{ekorg = t001w}ekorg )

into table lt_t001w

where t001w~vlfkz in lr_vlfkz and

t001w~ekorg in lr_ekorg.

- Tushar

Re: Inner Join

Former Member — Tue, 23 Nov 2010 04:50:04 GMT

I just realized there's duplicates entries in the return result...after deleting duplicates, it's fine.

Thanks for all your input.