topic Re: Help needed on SQL query... in Application Development and Automation Discussions

Help needed on SQL query...

Former Member — Mon, 14 Nov 2005 16:56:38 GMT

Hi,

I have the following SQL statement...I want to know how do I join table 1 to table 2 and table 1 to table 3 when

mseg is table 1

mkpf is table 2

zmigo_extend is table 3

The below statment is syntactically wrong..Please give me the correct way to right the INNER join between 3 tables...

SELECT a_{matnr a}werks a_{gpla a}wart

a_{menge a}dmbtr b_{budat c}txjcd INTO TABLE

migo_data

FROM mseg AS a INNER JOIN mkpf AS b ON a_{mblnr = b}mblnr zmigo_extend AS c INNER JOIN ON a_{mblnr = c}mblnr

WHERE a~matnr IN so_matnr

AND b~budat IN so_budat

AND a~werks IN so_werks

AND a~lgpla IN so_lgpla

AND a~bwart IN so_bwart

AND c~txjcd IN so_txjcd.

Re: Help needed on SQL query...

Former Member — Mon, 14 Nov 2005 17:03:55 GMT

SELECT a_{matnr a}werks a_{gpla a}wart

a_{menge a}dmbtr b_{budat c}txjcd INTO TABLE

migo_data

FROM mseg AS a INNER JOIN mkpf AS b ON a_{mblnr = b}mblnr INNER JOIN zmigo_extend AS c ON a_{mblnr = c}mblnr

WHERE a~matnr IN so_matnr

AND b~budat IN so_budat

AND a~werks IN so_werks

AND a~lgpla IN so_lgpla

AND a~bwart IN so_bwart

AND c~txjcd IN so_txjcd.

Check this one

Cheers,

Satya

Re: Help needed on SQL query...

Former Member — Mon, 14 Nov 2005 17:05:07 GMT

You can only use inner join between two tables. Try usin just JOIN for the third.

Re: Help needed on SQL query...

Former Member — Mon, 14 Nov 2005 17:07:34 GMT

Rad,

Sample:

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM <b>( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )</b>

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

Have a llok at this Link:

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/frameset.htm

So Ur Code:

SELECT a_{matnr a}werks a_{gpla a}wart

a_{menge a}dmbtr b_{budat c}txjcd

INTO TABLE migo_data

FROM <b>((mseg AS a

INNER JOIN mkpf AS b ON a_{mblnr = b}mblnr)

INNER JOIN zmigo_extend AS c ON a_{mblnr = c}mblnr)</b>

WHERE a~matnr IN so_matnr

AND b~budat IN so_budat

AND a~werks IN so_werks

AND a~lgpla IN so_lgpla

AND a~bwart IN so_bwart

AND c~txjcd IN so_txjcd.

Thanks

Kam

Note: Allot points for all worthful postings

Message was edited by: Kam

Re: Help needed on SQL query...

Former Member — Mon, 14 Nov 2005 17:58:26 GMT

Hi,

I am not sure whether you copy pasted the same code what you have in your program, I observed the following in your query.

1. While selecting the fields from mseg you specified a_{gpla and a}wart, I think you are looking for the fields a_{lgpla and a}bwart.

2. While joining the third table the keyword INNER JOIN is used after the table name (zmigo_extend AS c INNER JOIN ON), which is incorrect.

Change your select as follows.

SELECT a_{matnr a}werks a_{lgpla a}bwart

a_{menge a}dmbtr b_{budat c}txjcd

INTO TABLE migo_data

FROM mseg AS a

INNER JOIN mkpf AS b

ON a_{mblnr = b}mblnr

INNER JOIN zmigo_extend AS c

ON a_{mblnr = c}mblnr

WHERE a~matnr IN so_matnr

AND b~budat IN so_budat

AND a~werks IN so_werks

AND a~lgpla IN so_lgpla

AND a~bwart IN so_bwart

AND c~txjcd IN so_txjcd.

Thanks

Giridhar

Message was edited by: Giridhar Nayudu