do a "JOIN" within a "LEFT OUTER JOIN"?

[Former Member]

Can you do a "Join" within a "LEFT OUTER JOIN"? My SQL is:

SELECT ol.plc_id + '|' + CAST(ol.orderlog_nbr AS varchar(12))
                                               AS orderlog_id,
       ol.seal_type,
       ol.bag_markings,
       ol.bag_tag                              AS tags,
       ol.charge_inspection_to_id,
       olbo.discharge_port_serial_nbr,
       olbo.loading_port_serial_nbr,
       ol.docs_sent_to_customer_date,
       CAST(NULL AS varchar(100))              AS instruction_note,
       ollid.export_document_nbr,
       ollid.carrier_bol,
       ollid.phyto_certificate,
       ollid.inspection_certificate,
       s1_country.export_declaration_nbr_label
  FROM s1_orderlog ol
       LEFT OUTER JOIN s1_orderlog_booking_order olbo
         ON ol.plc_id = olbo.plc_id
        AND ol.orderlog_nbr = olbo.order_nbr
       LEFT OUTER JOIN s1_orderlog_loading_instr_doc ollid
         ON ollid.orderlog_nbr = ol.orderlog_nbr
        AND ol.plc_id = ollid.plc_id
       LEFT OUTER JOIN s1_name_and_address
            JOIN s1_country
              ON s1_country.country_code = s1_name_and_address.country_code
         ON s1_name_and_address.name_and_address_id = ol.origin_id
;

It produces the following error: Could not execute statement. Syntax error near 'ON' on line 26 SQLCODE=-132, ODBC 3 State="42000"

Line 26 is: JOIN s1_country

[Breck_Carter]

When I run it it says line 26 is this line...

ON s1_name_and_address.name_and_address_id = ol.origin_id

[Breck_Carter]

I've never had much luck fiddling around with ( parentheses around join operators ).

You may want a derived table instead, which is a different way to use ( parentheses )...

SELECT ol.plc_id + '|' + CAST(ol.orderlog_nbr AS varchar(12))
                                               AS orderlog_id,
       ol.seal_type,
       ol.bag_markings,
       ol.bag_tag                              AS tags,
       ol.charge_inspection_to_id,
       olbo.discharge_port_serial_nbr,
       olbo.loading_port_serial_nbr,
       ol.docs_sent_to_customer_date,
       CAST(NULL AS varchar(100))              AS instruction_note,
       ollid.export_document_nbr,
       ollid.carrier_bol,
       ollid.phyto_certificate,
       ollid.inspection_certificate,
       s1_name_address_country.export_declaration_nbr_label
  FROM s1_orderlog ol
       LEFT OUTER JOIN s1_orderlog_booking_order olbo
         ON ol.plc_id = olbo.plc_id
        AND ol.orderlog_nbr = olbo.order_nbr
       LEFT OUTER JOIN s1_orderlog_loading_instr_doc ollid
         ON ollid.orderlog_nbr = ol.orderlog_nbr
        AND ol.plc_id = ollid.plc_id
       LEFT OUTER JOIN 
          ( SELECT s1_name_and_address.*,
                   s1_country.export_declaration_nbr_label 
              FROM s1_name_and_address
              JOIN s1_country
                ON s1_country.country_code
                 = s1_name_and_address.country_code
          ) AS s1_name_address_country
         ON s1_name_address_country.name_and_address_id = ol.origin_id
;

Caveat: I have NOT tested that.

[Former Member]

Breck: close...but no cigar!! When I execute this SQL: SELECT s1_name_and_address.*, s1_country.export_declaration_nbr_label FROM s1_name_and_address JOIN s1_country ON s1_country.country_code = s1_name_and_address.country_code it gives me the expected results.

But when I run the entire query it gives this error: Could not execute statement. Syntax error near 'SELECT' on line 24 SQLCODE=-131, ODBC 3 State="42000"

And. of course, line 24 is the inner query.

[Breck_Carter]

Please show the exact query that gave the "Syntax error near 'SELECT' on line 24".

I get a semantic error (expected), not a syntax error...

Could not execute statement.
Table 's1_orderlog' not found
SQLCODE=-141, ODBC 3 State="42S02"
Line 1, column 1

[VolkerBarth · Accepted solution]

If you want to embed a join within another join, use parantheses around the "inner" join, such as...

from A left join
   (B inner join C on B.c1 = C.c1)
   on A.x = B.x

Without parantheses, joins are interpreted from left to right, so in your case the "JOIN" is not expected because the LEFT JOIN's ON condition is missing.

[Former Member · Accepted solution]

Volker: I modified the SQL as suggested: LEFT OUTER JOIN s1_name_and_address (JOIN s1_country ON s1_country.country_code = s1_name_and_address.country_code ) ON s1_name_and_address.name_and_address_id = ol.origin_id

but it produces the same error.

FYI: Interactive SQL version 17.0.4, build 2053

[VolkerBarth · Accepted solution]

No, see my sample, the table has to be the first element within the parantheses, not the JOIN keyword, so the opening paranthesis must be before s1_name_and_address...

[Breck_Carter · Accepted solution]

Always show the exact code you are referring to... always... always... always... always... always... that's 5 "always", the maximum possible 🙂

[Former Member · Accepted solution]

got it 🙂