Inner Join with the same table two times

[Former Member]

Hi all,

I want to join these two select statement in one, it's possible?

Both selects works fine individually, but if i try to join it is wrong allways return to me sy-subrc = 4.

SELECT DISTINCT object_id "... ... ... 
       INTO CORRESPONDING FIELDS OF TABLE it_po
       FROM crmd_orderadm_h AS h   "Header Data
       INNER JOIN crmd_link AS l        "Link Data
        ON l~guid_hi EQ h~guid
       INNER JOIN bbp_pdorg AS o       " Org. Str
        ON l~guid_set EQ o~set_guid
       WHERE h~object_id IN r_po
         AND proc_org IN r_ekorg
         AND proc_group IN r_ekgrp
         AND posting_date IN r_date
         AND object_type EQ 'BUS2201'.

SELECT DISTINCT object_id "... ... ...
    INTO CORRESPONDING FIELDS OF TABLE it_po
    FROM crmd_orderadm_h AS h  "Header
    INNER JOIN crmd_link AS l       "Link Data
     ON l~guid_hi EQ h~guid
    INNER JOIN CRMD_PARTNER AS p  "Vendors
     ON l~guid_set EQ p~guid
    INNER JOIN but000 AS b
     ON b~partner_guid EQ p~partner_no
    WHERE h~object_id IN r_po
      AND posting_date IN r_date
      AND b~partner IN r_vendor
      AND object_type EQ 'BUS2201'.

The problem is i need to use two times the table CRMD_LINK to join the tables CRMD_PARTNER and BBP_PDORG.

If i try to do the following, don't work;

SELECT DISTINCT object_id "... ... ...
    INTO CORRESPONDING FIELDS OF TABLE it_po
    FROM crmd_orderadm_h AS h  "Header
    INNER JOIN crmd_link AS l       "Link Data
     ON l~guid_hi EQ h~guid
    INNER JOIN bbp_pdorg AS o       " Org. Str
        ON l~guid_set EQ o~set_guid
    INNER JOIN CRMD_PARTNER AS p  "Vendors
     ON l~guid_set EQ p~guid
    INNER JOIN but000 AS b
     ON b~partner_guid EQ p~partner_no
    WHERE h~object_id IN r_po
      AND proc_org IN r_ekorg
      AND proc_group IN r_ekgrp
      AND posting_date IN r_date
      AND b~partner IN r_vendor
      AND object_type EQ 'BUS2201'.

Is it possible to merge these two selects?

Thanks in advance.

[Former Member]

Hi all,

I want to join these two select statement in one, it's possible?

Both selects works fine individually, but if i try to join it is wrong allways return to me sy-subrc = 4.

SELECT DISTINCT object_id "... ... ... 
       INTO CORRESPONDING FIELDS OF TABLE it_po
       FROM crmd_orderadm_h AS h   "Header Data
       INNER JOIN crmd_link AS l        "Link Data
        ON l~guid_hi EQ h~guid
       INNER JOIN bbp_pdorg AS o       " Org. Str
        ON l~guid_set EQ o~set_guid
       WHERE h~object_id IN r_po
         AND proc_org IN r_ekorg
         AND proc_group IN r_ekgrp
         AND posting_date IN r_date
         AND object_type EQ 'BUS2201'.

SELECT DISTINCT object_id "... ... ...
    INTO CORRESPONDING FIELDS OF TABLE it_po
    FROM crmd_orderadm_h AS h  "Header
    INNER JOIN crmd_link AS l       "Link Data
     ON l~guid_hi EQ h~guid
    INNER JOIN CRMD_PARTNER AS p  "Vendors
     ON l~guid_set EQ p~guid
    INNER JOIN but000 AS b
     ON b~partner_guid EQ p~partner_no
    WHERE h~object_id IN r_po
      AND posting_date IN r_date
      AND b~partner IN r_vendor
      AND object_type EQ 'BUS2201'.

The problem is i need to use two times the table CRMD_LINK to join the tables CRMD_PARTNER and BBP_PDORG.

If i try to do the following, don't work;

SELECT DISTINCT object_id "... ... ...
    INTO CORRESPONDING FIELDS OF TABLE it_po
    FROM crmd_orderadm_h AS h  "Header
    INNER JOIN crmd_link AS l       "Link Data
     ON l~guid_hi EQ h~guid
    INNER JOIN bbp_pdorg AS o       " Org. Str
        ON l~guid_set EQ o~set_guid
    INNER JOIN CRMD_PARTNER AS p  "Vendors
     ON l~guid_set EQ p~guid
    INNER JOIN but000 AS b
     ON b~partner_guid EQ p~partner_no
    WHERE h~object_id IN r_po
      AND proc_org IN r_ekorg
      AND proc_group IN r_ekgrp
      AND posting_date IN r_date
      AND b~partner IN r_vendor
      AND object_type EQ 'BUS2201'.

Is it possible to merge these two selects?

Thanks in advance.

[deepak_dhamat]

hi ,

why i could not find this table BBP_PDORG

which version you are using ....

regards

Deepak.

Edited by: Deepak Dhamat on Nov 12, 2010 10:10 AM

[kesavadas_thekkillath]

Is it a self join what you need ?

[Former Member]

Hi,

Deepak, i'm using SRM 7.0.

Keshav.T, i never use self joins... I don't know if this is what i nedd.

May be the example i put is a bit complicated, i have a main table and two secundary tables, the relationship between the main table with the other two is with other table... then i need to do a join two times with this table... you got my point?

[deepak_dhamat]

Hi ,

Instead of Inner join then go Step by Step by collecting data from first two tables and

pass that table to another Query .

Ofcourse this will degrade your performance as you will be using for all entries , but still you will get all required details

Regards

Deepak.

[Former Member]

Yes i did it, and works fine...

But, i want to know if it's possible to do this in one select...

[Former Member]

It is possible but I'm not sure how it will affect the performance.

SELECT DISTINCT object_id "... ... ...

INTO CORRESPONDING FIELDS OF TABLE it_po

FROM crmd_orderadm_h AS h "Header Data

INNER JOIN crmd_link AS l "Link Data

ON l_{guid_hi EQ h}guid

WHERE EXISTS (

• code from your first query

SELECT DISTINCT object_id "... ... ...

FROM crmd_orderadm_h AS h "Header Data

INNER JOIN crmd_link AS l "Link Data

ON l_{guid_hi EQ h}guid

INNER JOIN bbp_pdorg AS o " Org. Str

ON l_{guid_set EQ o}set_guid

WHERE h~object_id IN r_po

AND proc_org IN r_ekorg

AND proc_group IN r_ekgrp

AND posting_date IN r_date

AND object_type EQ 'BUS2201' )

OR EXISTS (

• code from your second query

SELECT DISTINCT object_id "... ... ...

FROM crmd_orderadm_h AS h "Header

INNER JOIN crmd_link AS l "Link Data

ON l_{guid_hi EQ h}guid

INNER JOIN CRMD_PARTNER AS p "Vendors

ON l_{guid_set EQ p}guid

INNER JOIN but000 AS b

ON b_{partner_guid EQ p}partner_no

WHERE h~object_id IN r_po

AND posting_date IN r_date

AND b~partner IN r_vendor

AND object_type EQ 'BUS2201' ).

The code I've put together may not be 100% correct as I do not have SRM to test it; but does show what you need to do.

Basically what it is doing is returning the OBJECT_ID from the first join provided that it exists in the results of the two queries you had that worked individually. I would have done it slightly differently in a database system but ABAP doesn't seem to support union queries and SQL queries on the results of other SQL queries.

Regards,

John