topic Re: join query for three tables in Application Development and Automation Discussions

join query for three tables

Former Member — Fri, 15 Dec 2006 06:42:19 GMT

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 a_{authlname a}authlnam a_{authfname a}authfnam a_{isbn b}title

from ( bsauthors as a inner join bsbook as b on a_{isbn = b}isbn )

into corresponding fields of table books

where a_{authlname like authorlname or a}authfname like authorfname

order by a_{authlname a}authfname a~isbn.

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 06:44:51 GMT

select a_{authlname a}authlnam a_{authfname a}authfnam a_{isbn b}title

c~<fieldname>

from bsauthors as a inner join bsbook as b on a_{isbn = b}isbn

inner join <dbtab> as c on a_{<fieldname> = <C}FIELDNAME>

into corresponding fields of table books

where a_{authlname like authorlname or a}authfname like authorfname

order by a_{authlname a}authfname a~isbn.

REGARDS

SHIBA DUTTA

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 06:51:09 GMT

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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 06:51:46 GMT

Hi,

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

better create a datavbase view.

Regards,

pankaj singh

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 06:56:33 GMT

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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 06:59:36 GMT

Hello,

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

Regards,

Shehryar Dahar

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 07:00:29 GMT

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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 07:02:00 GMT

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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 07:03:24 GMT

Hi again,

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

regards

pankaj singh

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 07:42:23 GMT

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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 07:42:49 GMT

Hi Mr. Manoj & Pankaj

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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 07:55:09 GMT

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 x_{matnr x}mtart x_{matkl y}werks y_{lgort z}maktx

into corresponding fields of table itab

from ( ( mara as x inner join mard as y on x_{matnr = y}matnr )

inner join makt as z on x_{matnr = z}matnr ) 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

Re: join query for three tables

Former Member — Fri, 15 Dec 2006 12:25:07 GMT

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