Skip to Content
author's profile photo Former Member
Former Member

Select Query using Joins

Hi,

Please give me the Select query

I have to fetch EBELN,BUKRS,LIFNR,BSART,EKORG,EKGRP,WAERS,WKURS from EKKO and

EBELP,LGORT,MATKL ,WERKS,BEDNR,IDNLF,BPRME,NETPR from EKPO and

MENGE, SAKTO,AUFNR,ANLN1,GSBER,PS_PSP_PNR from EKKN and

LPEIN,EINDT,SLFDT, from EKET.

I have to fetch all the data into an single internal table(Mandatory) by a select query using Joins.

EKKO is having EBELN as primary key

EKPO is having EBELN,EBELP as primary key

EKKN is having EBELN,EBELP as primary key

EKET is having EBELN,EBELP as primary key

Can anybody Please give me the Select query.

With Regards

Ajay

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Apr 10, 2008 at 12:04 PM

    Hi ,

    I am sending this sample coed for the Inner joins .Hope this will help you:

    Inner joins using 3 tables

    Try this :-

    SELECT stpostlnr stpoidnrk mastmatnr maramtart stpo~menge

    INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

    JOIN stpo ON stpostlnr = maststlnr

    JOIN mara ON maramatnr = mastmatnr

    WHERE stpostlty = 'M' "AND stpoidnrk IN s_matnr

    AND mast~werks = 1000.

    Here s_matnr is a select-options on the selection-screen.

    Or this.

    Code:

    Select single VbrkBukrs VbrkKunrg Vbrk~Vbeln

    VbrkFkdat VbrkBstnk_Vf Vbrk~Zterm

    Tvzbt~Vtext

    VbakVbeln VbakBstdk

    LikpVbeln Likplfdat Likp~Lfuhr

    into w_vbrk

    from vbrk

    inner join Tvzbt on TvzbtZterm = VbrkZterm and

    Tvzbt~Spras = sy-langu

    Inner join Vbfa as SalesLnk

    on SalesLnk~vbeln = pu_vbeln and

    SalesLnk~vbtyp_v = c_order

    inner join Vbak on VbakVbeln = SalesLnkVbelv

    Inner join Vbfa as DeliveryLnk

    on DeliveryLnk~vbeln = pu_vbeln and

    DeliveryLnk~vbtyp_v = c_Delivery

    inner join Likp on LikpVbeln = DeliveryLnkVbelv

    where vbrk~vbeln = pu_Vbeln.

    This code locates sales, delivery and payment terms info from a billing document number.

    or

    Here, this one also works fine :

    select zfpcdcadivi zfpcdproforma zfpcdfactura zfpcdaniofactura

    zfpcdmontousd zfpcdmontoap zfpcdebeln zfpcdinco1

    zfpcdlifnr lfa1name1 zcdvsstatus zfpcdconint

    into it_lista

    from zfpcd inner join zcdvs

    on zfpcdebeln = zcdvsebeln

    and zfpcdproforma = zcdvsproforma

    and zfpcdlifnr = zcdvslifnr

    inner join lfa1

    on zfpcdlifnr = lfa1lifnr

    where zcdvs~status = '04'.

    Also Here is another solution that just uses inner joins:

    SELECT vbakvbeln vbfaerdat INTO (itab-vbeln, itab-wadat)

    FROM ( vbak INNER JOIN vbap

    ON vbapvbeln = vbakvbeln )

    INNER JOIN vbfa ON vbakvbeln = vbfavbelv

    WHERE vbak~kunnr = m_wm AND

    vbak~vbtyp = 'C' AND

    vbfa~erdat IN s_date AND

    vbap~matnr = 'MZ1807F' AND

    vbfaposnv = vbapposnr AND

    vbfa~vbtyp_n = 'J'.

    <REMOVED BY MODERATOR>

    Cheers,

    Chandra Sekhar.

    Edited by: Alvaro Tejada Galindo on Apr 10, 2008 5:20 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 10, 2008 at 12:33 PM

    Hi,

    You can write in this way

    select a~ebeln

    b~ebelp

    c~menge

    d~lpein

    into table it_data

    from ( ( ( ekko as a join ekpo as b

    on aebeln = bebeln )

    join ekkn as c on bebeln = cebeln and

    bebelp = cebelp )

    join eket as d on cebeln = debeln and

    cebelp = debelp ).

    <REMOVED BY MODERATOR>

    Thaks,

    Suman

    Edited by: Alvaro Tejada Galindo on Apr 10, 2008 5:20 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 10, 2008 at 12:55 PM

    Hi ,

    I also had written the same query But so much Duplicate data is fetching.

    Pleae give me the some good query

    Regards

    Ajay

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 10, 2008 at 01:10 PM

    Hi,

    Is it possible using Outer joins

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi ajay

      if it is fetching a duplicate records then why u want to go in join way

      ABAP language has a funda of inter nal table

      make select query on all the 3 tables

      then create one final table

      loop and appen all records in it

      use for all entries it will delete duplicate records automatically..

      HTH

      <REMOVED BY MODERATOR>

      Edited by: Alvaro Tejada Galindo on Apr 10, 2008 5:21 PM

  • Posted on Apr 11, 2008 at 12:06 AM

    Hi Ajay,

    you can do this with outer joins. (Not the way I would do it).

    Anyway, try something like this.

      TYPES:
        BEGIN OF ty_pdoc,
          ebeln TYPE ekko-ebeln,
          bukrs TYPE ekko-ebeln,
          ebelp TYPE ekpo-ebelp,
          lgort TYPE ekpo-lgort,
          matkl type ekpo-matkl,
          zekkn TYPE ekkn-zekkn,
          menge TYPE ekkn-menge,
          etenr TYPE eket-etenr,
          lpein TYPE eket-lpein,
        END OF ty_pdoc.
    
      DATA: lt_pdocs TYPE TABLE OF ty_pdoc.
    
      SELECT a~ebeln
              a~bukrs
              b~ebelp
              b~lgort
              b~matkl
              c~zekkn
              c~menge
              d~etenr
              d~lpein
        INTO CORRESPONDING FIELDS OF TABLE lt_pdocs
        FROM ( ( ( ekpo AS b
           LEFT OUTER JOIN ekkn AS c ON c~ebeln = b~ebeln
                                   AND c~ebelp = b~ebelp )
           LEFT OUTER JOIN eket AS d ON d~ebeln = b~ebeln
                                   AND d~ebelp = b~ebelp )
           LEFT OUTER JOIN ekko AS a ON a~ebeln = b~ebeln ).
    

    Cheers

    Graham Robbo

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.