Problem with Left Outer Join

ekekakos — Fri, 24 Sep 2021 09:02:28 GMT

Dear all,

I found a lot question about the LEFT OUTER JOIN but allow me to add 1 more to make it more precise and if there is any other solution with open SQL

SELECT
    FROM but000 INNER JOIN kna1
        ON but000~partner = kna1~kunnr
      LEFT OUTER JOIN but0is AS doy
        ON but000~partner = doy~partner
      LEFT OUTER JOIN tb038b AS doy_description
        ON doy~istype = doy_description~istype AND
           doy~istype = '0001' AND
           doy~ind_sector = doy_description~ind_sector AND
           doy_description~spras = 'G'
      LEFT OUTER JOIN but0is AS bus_type
        ON OUTER but000~partner = bus_type~partner
      LEFT JOIN tb038b AS bus_type_descr
        ON bus_type~istype = bus_type_descr~istype AND
           bus_type~istype = '0003' AND
           bus_type~ind_sector = bus_type_descr~ind_sector AND
           bus_type_descr~spras = 'G'
    FIELDS but000~partner, but000~bu_group AS partner_group, kna1~name1 AS name, kna1~name2 AS surname,
           kna1~name3 AS father_name, kna1~name4 AS mother_name, kna1~stceg AS vat, kna1~stcd1 AS police_id,
           kna1~stras AS address, kna1~pstlz AS post_code, kna1~ort01 AS city,
           bus_type_descr~ind_sector AS bus_type,
           bus_type_descr~text AS bus_type_descr,
           doy_description~ind_sector AS doy,
           doy_description~text AS doy_dercr
    WHERE but000~partner = @partner

This one does not work even with 1 pair of LEFTs

SELECT
    FROM but000 INNER JOIN kna1
        ON but000~partner = kna1~kunnr
      LEFT OUTER JOIN but0is AS doy
        ON but000~partner = doy~partner
      LEFT OUTER JOIN tb038b AS doy_description
        ON doy~istype = doy_description~istype AND
           doy~istype = '0001' AND
           doy~ind_sector = doy_description~ind_sector AND
           doy_description~spras = 'G'

FIELDS but000~partner, but000~bu_group AS partner_group, kna1~name1 AS name, kna1~name2 AS surname,
           kna1~name3 AS father_name, kna1~name4 AS mother_name, kna1~stceg AS vat, kna1~stcd1 AS police_id,
           kna1~stras AS address, kna1~pstlz AS post_code, kna1~ort01 AS city,
           doy_description~ind_sector AS doy,
           doy_description~text AS doy_dercr
    WHERE but000~partner = @partner

I found the below answer

the left outer join can only from same table, E.G.

left outer join B to A

left outer join C to A

but can't

left outer join A to B

left outer join B to C

Are they correct and if yes how can do the above in a SQL query.

Thanks

Elias

Re: Problem with Left Outer Join

ekekakos — Fri, 24 Sep 2021 09:45:00 GMT

Finally the SQL query is not totally correct. The correct one is:

    SELECT
    FROM but000 INNER JOIN kna1
        ON but000~partner = kna1~kunnr
      LEFT OUTER JOIN but0is AS doy
        ON doy~partner = but000~partner AND
           doy~istype = '0001'                         " This is the change
      LEFT OUTER JOIN tb038b AS doy_description
        ON doy~istype = doy_description~istype AND
           doy~ind_sector = doy_description~ind_sector AND
           doy_description~spras = 'G'
      LEFT OUTER JOIN but0is AS bus_type
        ON but000~partner = bus_type~partner AND
           bus_type~istype = '0003'                    " This is the change
      LEFT OUTER JOIN tb038b AS bus_type_descr
        ON bus_type~istype = bus_type_descr~istype AND
           bus_type~ind_sector = bus_type_descr~ind_sector AND
           bus_type_descr~spras = 'G'
    FIELDS DISTINCT but000~partner, but000~bu_group AS partner_group, kna1~name1 AS name, kna1~name2 AS surname,
           kna1~name3 AS father_name, kna1~name4 AS mother_name, kna1~stceg AS vat, kna1~stcd1 AS police_id,
           kna1~stras AS address, kna1~pstlz AS post_code, kna1~ort01 AS city,
           bus_type~ind_sector AS business_type ,
           bus_type_descr~text AS business_type_descr,
           doy~ind_sector AS doy,
           doy_description~text AS doy_description
    WHERE (where_clause)
    INTO TABLE @get_business_partners-bu_partners

Thanks

Elias

topic Problem with Left Outer Join in Application Development and Automation Discussions

Problem with Left Outer Join

Re: Problem with Left Outer Join