Joins With 3 table

[ABAPER_P]

Hi all

I have 3 tables BKPF , BSID , BSAD.

BELNR document number will be present in either BSID (open doc ) or BSAD (cleared doc) .

Driving table here is BKPF. document number will be always present in BKPF table.

Now I need join condition if document number is present in any one of the table either BSID or BSAD I shoud get records in internal table . I have created inner join of all these 3 tables but its not woking

[ABAPER_P]

Hi all

I have 3 tables BKPF , BSID , BSAD.

BELNR document number will be present in either BSID (open doc ) or BSAD (cleared doc) .

Driving table here is BKPF. document number will be always present in BKPF table.

Now I need join condition if document number is present in any one of the table either BSID or BSAD I shoud get records in internal table . I have created inner join of all these 3 tables but its not woking

[Former Member]

Hi ABAPER P,

Try below Code.

SELECT a~bukrs
a~belnr
a~gjahr
FROM bkpf AS a
JOIN bsid AS b ON b~belnr = a~belnr
LEFT OUTER JOIN bsad AS c ON c~belnr = a~belnr
INTO TABLE it_tab
WHERE a~bukrs = '1200'
AND a~belnr IN p_belnr
AND a~gjahr = '2018'.

Thanks,

Surji

[nick_giannakis]

And if document exists in BSAD and not in BSID the above select will fail...

[nick_giannakis]

On what "SAP_ABA" component you are working?

[nick_giannakis]

Trying to go with surji.murugesan's answer a little bit further,

SELECT a~bukrs,
       a~belnr,
       a~gjahr
       FROM bkpf AS a
       INNER JOIN bsid AS b ON b~belnr = a~belnr
                           AND b~gjahr = a~gjahr
WHERE a~bukrs = '1200'
  AND a~belnr = @p_belnr
  AND a~gjahr = '2018'

UNION
SELECT a~bukrs,
       a~belnr,
       a~gjahr
       FROM bkpf AS a
       INNER JOIN bsad AS b ON b~belnr = a~belnr
                           AND b~gjahr = a~gjahr
WHERE a~bukrs = '1200'
  AND a~belnr = @p_belnr
  AND a~gjahr = '2018'

  INTO TABLE @it_tab.

Maybe for someone will not be the ideal solution, but will do the trick.

[Nawanandana]

Hi,

you can use FM to fetch the data from those table

Regards,

Nawa

[SimoneMilesi]

You already pointed out the issue

Now I need join condition if document number is present in any one of the table either BSID or BSAD I shoud get records in internal table . I have created inner join of all these 3 tables but its not woking

Since a record will be in BSAD ---OR--- BSID, if you use INNER JOIN, you'll never get anything, because the record would not be present in both of them

[juan_suros]

Why not join BKPF to BSEG?

The helper tables BSID and BSAD each hold a subset of the data in BSEG. BSEG holds all of the accounting document segments both closed and not closed.