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: 

fetch three tables

Former Member
0 Kudos
108

Is it possible to fetch 3 tables with single select query(without using view)

1 ACCEPTED SOLUTION

Former Member
0 Kudos
62

We can use JOIN to select from 2 or more tables.

But JOIN wont work for pooled/cluster tables

8 REPLIES 8

Former Member
0 Kudos
62

Hi,

Yes it is possible through JOIN.

check the below code

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT ccarrname pconnid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON pcarrid = ccarrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON fcarrid = pcarrid

AND fconnid = pconnid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Message was edited by:

Velangini Showry Maria Kumar Bandanadham

0 Kudos
62

Inner joins are obsolete so i don't want to use them without using "view" and "joins" is it possible to fetch records from 3 tables using single select query.

Former Member
0 Kudos
63

We can use JOIN to select from 2 or more tables.

But JOIN wont work for pooled/cluster tables

Former Member
0 Kudos
62

hi

good

use the join statement to join any number of table using a single select statement.

thanks

mrutyun^

Former Member
0 Kudos
62

Hi,

Yes, you can use JOIN or FOR ALL ENTRIES. Performance wise, For All Entries is better.

Try like this:

<u><b>JOIN:</b></u>

select zfpcdcadivi zfpcdproforma zfpcdfactura zfpcdaniofactura

zfpcdmontousd zfpcdmontoap zfpcdebeln zfpcdinco1

zfpcdlifnr lfa1name1 zcdvsstatus zfpcdconint

into it_lista

from zfpcd inner join zcdvs

on zfpcdebeln = zcdvsebeln

and zfpcdproforma = zcdvsproforma

and zfpcdlifnr = zcdvslifnr

inner join lfa1

on zfpcdlifnr = lfa1lifnr

where zcdvs~status = '04'.

<u><b>FOR ALL ENTRIES:</b></u>

SELECT likpvbeln likplifex likpbldat likpwadat likpwadat_ist likpkodat likp~lfart

likpkunnr likpvstel lipsposnv lipslfimg lipsvrkme lipslgmng lips~meins

lipswerks lipslgort lipscharg lipsvbelv lipsposnr lipsmatnr

lipsvbeln LIPSVGBEL LIPSVGPOS vbupkosta vbupwbsta vbupposnr vbup~vbeln

  • VBAKIHREZ VBAKVBELN VBAP~VBELN

INTO CORRESPONDING FIELDS OF TABLE it_itab

FROM ( likp

INNER JOIN lips

ON lipsvbeln = likpvbeln

INNER JOIN vbup

ON vbupposnr = lipsposnr

and VBUPVBELN = LIPSVBELN )

  • left outer join VBAK

  • on VBAKVBELN = LIPSVGBEL

  • inner join VBAP

  • on VBAPVBELN = VBAKVBELN )

WHERE likp~vbeln IN so_vbeln

AND likp~lifex IN so_lifex

AND likp~lfart IN so_lfart

AND likp~kunnr IN so_kunnr

AND likp~vstel IN so_vstel

AND likp~bldat IN so_bldat

AND likp~wadat_ist IN so_wadat

AND vbup~kosta IN so_kosta

AND vbup~wbsta IN so_wbsta

AND LIPS~LFIMG NE 0.

Regards,

Bhaskar

Former Member
0 Kudos
62

HI,

YOU CAN USE THE INNER JOIN QUERY AS SYNTAX FOLLOWS

<b>SELECT A<field1> A<field2> B<field1> B<field2> C<field1> C<field2>

INTO TABLE ITAB

FROM (MARA AS A INNER JOIN MAKT AS B

ON AFIELD1 = BFIELD1) INNER JOIN MARC AS C ON ON AFIELD1 = CFIELD1</b>.

Former Member
0 Kudos
62

YES IT CAN BE POSSIBLE BY JOINES AND FOR ALL ENTRIES

IF U WANT GOOD PERFORMANCE GO FOR FOR ALL ENTRIES

Former Member
0 Kudos
62

Inner joins are obsolete so i don't want to use them without using "view" and "joins" is it possible to fetch records from 3 tables using single select query.

I don't think i got a proper answer to the above ques.