joininig SQL statement

[Former Member]

Hi all,

I want to join 2 db tables (A, B), and select a minimum column (A~field1) from db table A, what should be the sql statement? Thanks.

Select (....)

into corresponding fields of itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

where (....).

append itab.

Endselect

[Former Member]

Select ekko~field1 ekpo~field2 
min( eket~field3 )
into corresponding fields of TABLE itab
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp 
group by ekko~field1 ekpo~field2

[suresh_datti]

Hi Macy,

try this..

Select (....)

into corresponding fields of table itab

from ekko as A

inner join ekpo as B

on A_{ebeln = B}ebeln

where (....).

Regards,

Suresh Datti

[Former Member]

Select (....)
into corresponding fields of itab
from ekko as A
join ekpo as B
on A~ebeln = B~ebeln
where A~field1 = ( SELECT min(field1) from A ).

append itab. 
Endselect

Hope it works...

[former_member186741]

Your prototype select is pretty much ok, you need the MIN aggregate function:

Select MIN (....) AS ??? "??? = SAME NAME AS COLUMN IN ITAB

into corresponding fields of TABLE itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

where (....).

Message was edited by: Neil Woodruff

[Former Member]

Sorry,

I should have said that I have 3 db tables (A, B, C) to join.

And I'd like to get the min fieldX value from C.

What should be the SQL statement?

Select A_{field1 B}field2

min( C~fieldX)

into corresponding fields of TABLE itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebelp

where (....).

append itab.

Endselect.

[former_member186741]

Select A_{field1 B}field2

min( C~fieldX) as fieldX

into corresponding fields of TABLE itab

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebelp

where (....).

• no append or endselect required

[Former Member]

Thanks.

I've followed the code.

But there is syntax error:

The field "B_{MEINS" from the SELECT list is missing in the GROUP BY clause. addition INTO wa or INTO (g1,...gn) is required. fields of type "" or "A}MANDT".

[former_member186741]

try again, for example:

Select A_{ebeln B}ebelp c~etenr

min( C~eindt ) as eindt

into corresponding fields of table tt

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebel

group by a_{ebeln b}ebelp c~etenr

.

[Former Member]

This works ...

TABLES : ekko.

DATA : begin of i_data occurs 0,
          ebeln LIKE ekko-ebeln,
       end of i_data.

select ekko~ebeln
  into table i_data
  from ekko join ekpo on ekko~ebeln = ekpo~ebeln
where ekko~field1 IN ( SELECT min( field1 ) from ekko ).

Is this what you want?

[Former Member]

Select ekko~field1 ekpo~field2 
min( eket~field3 )
into corresponding fields of TABLE itab
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp 
group by ekko~field1 ekpo~field2

[Former Member]

DATA : begin of i_data occurs 0,
          field1 LIKE ekko-field1,
          field2 LIKE ekpo-field2,
          menge LIKE eket-menge,
       end of i_data.

Select ekko~field1 ekpo~field2
min( eket~menge )
into  TABLE i_data
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp
group by ekko~field1 ekpo~field2

Check whether it works and please let us know.

[Former Member]

Then where could I put the "where clause" ?

[former_member186741]

Select A_{ebeln B}ebelp c~etenr

min( C~eindt ) as eindt

into corresponding fields of table tt

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebel

where .......

group by a_{ebeln b}ebelp c~etenr

.

[Former Member]

So many thanks to all of you.

It works. But the result in the itab is unexpected.

I want to retrieve the entry with minimum eindt into table itab, but all the entry (not only min.) are retrieved into itab.

[former_member186741]

Hi Macy,

this sql gives you the minimun eindt for each ETENR not the MIN for the whole set. What is it that you wanted?

[Former Member]

Hi Neil,

I want to get 1 entry with its minimum delivery date MIN( EINDT ) and other info for each PO item

[former_member186741]

then try,

Select A_{ebeln B}ebelp c~etenr

C~eindt as eindt

into corresponding fields of table tt

from ekko as A

join ekpo as B

on A_{ebeln = B}ebeln

join eket as C

on B_{ebeln = C}ebeln and B_{ebelp = C}ebelp

where eindt IN ( SELECT min( eindt ) from eket )

.

But, this gives you the lowest EINDT in the whole EKET table which is not necessarily what you want. I think you want the MIN (EINDT) of the ones you have selected but I'm not sure how to achieve that.

Message was edited by: Neil Woodruff

[Former Member]

If you want the minimum value of menge for ebeln

Then use...

Select ekko~ebeln
min( eket~menge )
into corresponding fields of TABLE i_data
from ekko
join ekpo
on ekko~ebeln = ekpo~ebeln
join eket
on ekpo~ebeln = eket~ebeln and ekpo~ebelp = eket~ebelp
where ....
group by ekko~ebeln.