fetch three tables

[Former Member]

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

[Former Member]

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 c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

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

ENDLOOP.

Message was edited by:

Velangini Showry Maria Kumar Bandanadham

[Former Member]

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]

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

But JOIN wont work for pooled/cluster tables

[Former Member]

hi

good

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

thanks

mrutyun^

[Former Member]

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 zfpcd_{cadivi zfpcd}proforma zfpcd_{factura zfpcd}aniofactura

zfpcd_{montousd zfpcd}montoap zfpcd_{ebeln zfpcd}inco1

zfpcd_{lifnr lfa1}name1 zcdvs_{status zfpcd}conint

into it_lista

from zfpcd inner join zcdvs

on zfpcd_{ebeln = zcdvs}ebeln

and zfpcd_{proforma = zcdvs}proforma

and zfpcd_{lifnr = zcdvs}lifnr

inner join lfa1

on zfpcd_{lifnr = lfa1}lifnr

where zcdvs~status = '04'.

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

SELECT likp_{vbeln likp}lifex likp_{bldat likp}wadat likp_{wadat_ist likp}kodat likp~lfart

likp_{kunnr likp}vstel lips_{posnv lips}lfimg lips_{vrkme lips}lgmng lips~meins

lips_{werks lips}lgort lips_{charg lips}vbelv lips_{posnr lips}matnr

lips_{vbeln LIPS}VGBEL LIPS_{VGPOS vbup}kosta vbup_{wbsta vbup}posnr vbup~vbeln

• VBAK_{IHREZ VBAK}VBELN VBAP~VBELN

INTO CORRESPONDING FIELDS OF TABLE it_itab

FROM ( likp

INNER JOIN lips

ON lips_{vbeln = likp}vbeln

INNER JOIN vbup

ON vbup_{posnr = lips}posnr

and VBUP_{VBELN = LIPS}VBELN )

• left outer join VBAK

• on VBAK_{VBELN = LIPS}VGBEL

• inner join VBAP

• on VBAP_{VBELN = VBAK}VBELN )

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]

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 A_{FIELD1 = B}FIELD1) INNER JOIN MARC AS C ON ON A_{FIELD1 = C}FIELD1</b>.

[Former Member]

YES IT CAN BE POSSIBLE BY JOINES AND FOR ALL ENTRIES

IF U WANT GOOD PERFORMANCE GO FOR FOR ALL ENTRIES

[Former Member]

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.