help please

[Former Member]

I've 2 tables .... table #1 have 4 fields out of which 2 fields only present in the table #2 ...... can I output the data of all the fields that are in both the tables.

thanks in advace.

[Former Member]

Hi Shaheen,

Are the two fields in the table 1 and 2 are Key fields?? What are the key fields present in both the tables ? If they are key fields we can retrive the data from the two tables...

Srini

[Former Member]

thanks for your reply ..... no they are not the key fields.

[Former Member]

Try using Joining the tables by Inner Join to retrieve the data

[Former Member]

how can we retrieve the data if they were the key fields

[athavanraja]

if there are no common values to compare between table 1 and table 2 how do you want to combine records? may be first record in tab1 with first rec in tab 2?

[Former Member]

no what i ment is that there are common fields between the 2 but not all of the are the key fields.

[Former Member]

check this example... combination of fields from two tables....

SELECT P_{CARRID P}CONNID F~FLDATE

INTO TABLE ITAB

FROM SPFLI AS P

INNER JOIN SFLIGHT AS F ON P_{CARRID = F}CARRID AND

P_{CONNID = F}CONNID

<b>WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK'</b>.

in the join give the link between two tables.

regds,

kiran

[Former Member]

you give the fields which are common in the two tables in the condition inner join,

as my above example.

in that example carrid and connid are common in both the tables sflight and spfli.

regds,

kiran

[Former Member]

ok .... what should i do next

[Former Member]

declare all the required fields into one internal table.

assume first table as spfli and second table as sflight

write the code in this format.

SELECT P_{CARRID P}CONNID F~FLDATE

INTO TABLE ITAB

FROM SPFLI AS P

INNER JOIN SFLIGHT AS F ON P_{CARRID = F}CARRID AND

P_{CONNID = F}CONNID

WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK'.

1. the p_{carrid p}connid f~fldate are the fields which are required from two tables

2. inner join sflight as f on p_{carrid on = f}carrid and p_{connid = f}connid.

is the condition between two tables.

if you are not yet clear , give me the fields and table names which you want to query.

regds,

kiran

[athavanraja]

the inner join Kiran is referring is for database tables.

for itabs

let us assume you have

tab1 with 4 fields

tab 2 with 2 fields

field 1 in tab1 and field 1 in tab2 are the connecting values.

and you wan to move them to new itab

loop at itab1.

read table itab2 with key field1 = itab1-field1

if sy-subrc eq 0 .

move: fileds from itab1 and itab2 to new itab.

append new itab.

endif .

endloop .

REgards

Raja

[Former Member]

my tables are mlgt and mlgn.

[Former Member]

these are database tables.

i do not have access to SAP system right now. if possible can u give the fields of both tables, so that i can make a query.

otherwise do like this....

data itab1 like table of mlgt with header line.

data itab2 like table of mlgn with header line.

(delcare itab3 with all the fields you required).

select * from mlgt into table itab1.

select * from mlgn into table itab2

loop at itab1.

read table itab2 with <col> = itab1-<col>

<col> = itab1-<col>

if sy-subrc eq 0.

move all itab1 and itab2 to itab3(final table).

append itab3.

endif.

endloop.

loop at itab3.

write : / itab3-............

endloop.

in the above example change <col> with the existing columns in two tables.

regds,

kiran

[Former Member]

Hi Shaheen,

You can use inner join like this: This is only example you need to use according to your main table & details table.

SELECT MLGN_{MATNR MLGN}LGNUM

INTO TABLE ITAB

FROM MLGN

INNER JOIN MLGT ON MLGN_{MATNR = MLGT}MATNR AND

MLGN_{LGNUM = MLGT}LGNUM

WHERE MLGN~LGNUM= '1000'.

For more information on INNER JOINS I like to tell to you that in real time scenario generally dont use INNER JOINS because of Database performance. Instead of this SELECT FOR ALL ENTRIES is preferable.

Ashven

[Former Member]

not yet solved ..... help

[seshatalpasai_madala]

Hi,

Try this query,

DATA: it_tab type table of mlgn.

SELECT MLGN_{MATNR MLGN}LGNUM

FROM MLGN

INNER JOIN MLGT ON MLGN_{MATNR = MLGT}MATNR AND

MLGN_{LGNUM = MLGT}LGNUM INTO CORRESPONDING FIELDS OF IT_TAB

WHERE MLGN~LGNUM= '1000'.

Regards,

Sesh

[Former Member]

still not getting the required

[Former Member]

can I have the syntax for SELECT FOR ALL ENTRIES

[Former Member]

Data: begin of it_mlgn occurs 0,

matnr like mlgn-matnr,

lgnum like mlgn-lgnum,

lgbkz like mlgn-lgbkz,

end of it_mlgn.

data: begin of it_mlgt occurs 0,

matnr like mlgt-matnr,

lgnum like mlgt-lgnum,

lgpla like mlgt-lgpla,

end of it_lgpla.

select matnr lgnum gbkz from mlgn into table it_mlgn

where ...

if not it_mlgn[] is initial.

sort it_mlgn by matnr lgnum.

select matnr lgnum lgpla from mlgt from mlgt

into table it_mlgt

for all entries in it_mlgn

where matnr = it_mlgn-matnr

lgnum = it_mlgn-lgnum.

endif.

[Former Member]

once you get the data from MLGN into one internal table(itab1).

then you can use for all entries in this way

<b>select * from MLGT into table itab2

for all entries in itab1

where matnr = itab1-matnr and

lgnum = itab1-lgnum.</b>

once you get the data into itab1 and itab2, then

loop at itab1.

read table itab2 with matnr = itab1-matnr

lgnum = itab1-lgnum.

if sy-subrc eq 0.

(here you write all itab1 and itab2 fields)

endif.

endloop.

the one in the bold letters is the code for all entries.

regds,

kiran

[Former Member]

read table itab2 with matnr = itab1-matnr

lgnum = itab1-lgnum.

gives an error unable to interpert "MATNR".

[athavanraja]

i gave you the code yesterday itself

check by previous posting.

read table itab2 with <b>key</b> field1 = itab1-field1

you are missing 'KEY'

[Former Member]

read table it_mlgt with key matnr = it_mlgt-matnr and lgnum = it_mlgt-lgnum.

gives an error as well.

[Former Member]

read table it_mlgt with key matnr = itab1-matnr

lgnum = itab1-lgnum.

here itab1-matnr & itab1-lgnum, should be outer loop fields i.e,

loop at itab1.

read table it_mlgt with key matnr = itab1-matnr

lgnum = itab1-lgnum.

endloop.

regds,

kiran

[athavanraja]

read table it_mlgt with key matnr = it_mlgt-matnr and lgnum = it_mlgt-lgnum.

remove the and

it should be

read table it_mlgt with key matnr = it_mlgt-matnr lgnum = it_mlgt-lgnum.

Raja

Assign points for helpful answers