duplicate value in Inner join

[Former Member]

hi all,

i m using inner join for 3 tables but problem is that i m getting repeated values while table contains only

2 record but inner joi shows me 4.

codes : SELECT m1_{pernr m2}lgart m2_{endda m2}betrg m2_{zuord m3}lga01 m3_{bet01 m3}begda INTO TABLE itab

FROM pa0000 AS m1 INNER JOIN pa0015 AS m2 ON m1_{pernr = m2}pernr

INNER JOIN pa0589 AS m3 ON m1_{pernr = m3}pernr

WHERE m1_{pernr IN pernr AND m1}endda = '99991231' AND m1~stat2 = '3'

AND m2~lgart = '4004'

AND m3~lga01 = '4004'.

getting values in itab like :

PERNR LGART BETRG BET01

8430 4004 5834.00 5004.00

8430 4004 8748.00 5004.00

8430 4004 5834.00 1000.00

8430 4004 8748.00 1000.00

while it must be like this :

PERNR LGART BETRG BET01

8430 4004 5834.00 5004.00

8430 4004 8748.00 1000.00

can anyone please help me.

regards saurabh.

[Former Member]

hi all,

i m using inner join for 3 tables but problem is that i m getting repeated values while table contains only

2 record but inner joi shows me 4.

codes : SELECT m1_{pernr m2}lgart m2_{endda m2}betrg m2_{zuord m3}lga01 m3_{bet01 m3}begda INTO TABLE itab

FROM pa0000 AS m1 INNER JOIN pa0015 AS m2 ON m1_{pernr = m2}pernr

INNER JOIN pa0589 AS m3 ON m1_{pernr = m3}pernr

WHERE m1_{pernr IN pernr AND m1}endda = '99991231' AND m1~stat2 = '3'

AND m2~lgart = '4004'

AND m3~lga01 = '4004'.

getting values in itab like :

PERNR LGART BETRG BET01

8430 4004 5834.00 5004.00

8430 4004 8748.00 5004.00

8430 4004 5834.00 1000.00

8430 4004 8748.00 1000.00

while it must be like this :

PERNR LGART BETRG BET01

8430 4004 5834.00 5004.00

8430 4004 8748.00 1000.00

can anyone please help me.

regards saurabh.

[Former Member]

Hi,

Use FOR ALL ENTRIES Statement.

Duplicates will get deleted automatically.

Vikas.

[Former Member]

hi all,

thanx for reply, actually this is my first query so i can use for all entries, there are only one record in table PA0000 and 2 in PA0015 and 2 is also in PA0589. so i need only two records in itab. but this join is giving 4 records. can anyone please help me?

[Former Member]

You will have to match all the primary keys of the three tables.

regards,

Jinson.

[Former Member]

Else.

use DELETE ADJACENT DUPLICATES statement.

Vikas.

[Former Member]

Hello Saurabh,

Include all the primary key fields in the where clause for PA0015 and PA0589, while comparing with those in PA0000. Since the key fields for all these '3' tables are same this should not be a problem.

If you have any reservations about changing the select query, then, sort your final internal table with a defined sort key (based on your requirement) and use "Delete adjacent duplicates" with the same sort key.

Please let me know if this helps.

Regards,

Pavan