topic Re: fetch three tables in Application Development and Automation Discussions

fetch three tables

Former Member — Fri, 13 Jul 2007 07:42:26 GMT

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

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 07:46:17 GMT

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

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 07:50:37 GMT

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

But JOIN wont work for pooled/cluster tables

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 09:15:16 GMT

good

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

thanks

mrutyun^

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 09:23:39 GMT

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.

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 09:24:43 GMT

Hi,

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

Try like this:

JOIN:

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'.

FOR ALL ENTRIES:

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

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 09:29:52 GMT

HI,

YOU CAN USE THE INNER JOIN QUERY AS SYNTAX FOLLOWS

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.

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 09:33:12 GMT

YES IT CAN BE POSSIBLE BY JOINES AND FOR ALL ENTRIES

IF U WANT GOOD PERFORMANCE GO FOR FOR ALL ENTRIES

Re: fetch three tables

Former Member — Fri, 13 Jul 2007 09:59:02 GMT

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.