Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

join query for three tables

Former Member
0 Kudos

Hi i have got this query that is a join of two tables but i need join for three tables i need the extended query

select aauthlname aauthlnam aauthfname aauthfnam aisbn btitle

from ( bsauthors as a inner join bsbook as b on aisbn = bisbn )

into corresponding fields of table books

where aauthlname like authorlname or aauthfname like authorfname

order by aauthlname aauthfname a~isbn.

12 REPLIES 12

Former Member
0 Kudos

select aauthlname aauthlnam aauthfname aauthfnam aisbn btitle

c~<fieldname>

from bsauthors as a inner join bsbook as b on aisbn = bisbn

inner join <dbtab> as c on a<fieldname> = <CFIELDNAME>

into corresponding fields of table books

where aauthlname like authorlname or aauthfname like authorfname

order by aauthlname aauthfname a~isbn.

REGARDS

SHIBA DUTTA

Former Member
0 Kudos

Hi Pavan,

It is not right practise to join three tables using ABAP code.

For better performance create a view and write a query.

1) Create a database view for 3 tables.

2) Write a select query on the database view.

Regards

Bhupal Reddy

Former Member
0 Kudos

Hi,

its not recommended that u go for three tables inner join for performance issue

better create a datavbase view.

Regards,

pankaj singh

0 Kudos

Hi Mr. Pankaj singh i'll follow your instructions in creating a new database view but as i'm new to this ABAP i dont know how to create a new data base view so that i can create that and it may be usefull to solve my problem completely

0 Kudos

Hello Pavan,

Go to transaction SE11, select the option view and enter a view name and click on Create. Select database view from the options available and click on Copy.

Enter a description for the view and in the Tables column, enter a table name and click on the Relationships button. All the related tables for the table entered will be displayed. Select the tables that you need to join and click on Copy.

Select the fields that you need from View flds tab and enter any conditions in the Selection Conditions tab and activate your view.

Regards,

Manoj

0 Kudos

Hi Mr. Manoj & Pankaj

i had proceeded in your instructions but after declaring the three tables(EKKO,EKBE,LFA1) in the tables column it was giving the relation only for the table which i had defined in the last of the tables column

i mean if i had declared the tables in this order

EKBE

EKKO

LFA1

then it was displaying only the tables related with LFA1 but not with other tables this is the case in every time i enter the tables into the tables column but i want the relation for all the three

Former Member
0 Kudos

Hello,

Its not advisable to join three tables together and secondly, use GROUP BY instead of ORDER BY.

Regards,

Shehryar Dahar

Former Member
0 Kudos

Hi,

to create a database view -

goto se11

select view radiobutton - give a name -create

select database view-enter

give the description

in tables filed give all the tables one under the other-check all these and click on relationship at the down

it will show the relations betn the tables select all of them and it will come at right hand side join conditions

goto next tab-view fields -select the fields out of all the tables including primary keys which you want to display

save and activate it.

regards,

pankaj singh

Former Member
0 Kudos

Hi again,

and use this view like any other database table you use in your select query to fetch data.

regards

pankaj singh

0 Kudos

Hi Mr. Manoj & Pankaj

i had proceeded in your instructions but after declaring the three tables(EKKO,EKBE,LFA1) in the tables column it was giving the relation only for the table which i had defined in the last of the tables column

i mean if i had declared the tables in this order

EKBE

EKKO

LFA1

then it was displaying only the tables related with LFA1 but not with other tables this is the case in every time i enter the tables into the tables column but i want the relation for all the three

Former Member
0 Kudos

Hi pavan

Plz try the following querry for joins on three tables :

" Customize it to ur requirements and tablenames

Parameters : b type mara-matnr.

select xmatnr xmtart xmatkl ywerks ylgort zmaktx

into corresponding fields of table itab

from ( ( mara as x inner join mard as y on xmatnr = ymatnr )

inner join makt as z on xmatnr = zmatnr ) where x~matnr = a and

y~werks

= b .

Plz see that there is atleast one field common between the three tables u want to use.

Regards

Pankaj

0 Kudos

Hi Mr. Pankaj i have created a database view on your instructions it was executed with small warnings but the values in that tables are not transfered to this database view while activating it was showing some warnings and after that i had tried to execute that i tried see the values in that but there are no values in the database view so what to do know please give me any suggestions