title: DB2 left join query difficulties
body: <p>I am working to prepare what should be a relatively simple query through ODBC on an iSeries server.  I want it to return a segment of the data no matter what the join is for a report we are creating.  Here is the query:</p>
  
  <pre><code>  Select   SCDATA.SCCLNT.CCLNT,
    SCDATA.SCCLNT.CNAME,
    SCDATA.SCCLNT.CLTGRP As Group,
    CONCAT(TRIM(SCDATA.SCCLNT.CADD1), SCDATA.SCCLNT.CADD2) As Address1,
    CONCAT(TRIM(SCDATA.SCCLNT.CCITY), CONCAT(', ',
        CONCAT(TRIM(SCDATA.SCCLNT.CSTATE), CONCAT('  ', 
        TRIM(SCDATA.SCCLNT.CZIP)))))
        As Address2,
    SCDATA.SCACCT.GCLNT,
    SCDATA.SCACCT.GPLACE,
    SCDATA.SCACCT.GPLDAT,
    SCDATA.SCACCT.GACCT# As GACCTnum,
    CONCAT(TRIM(SCDATA.SCACCT.GLAST), CONCAT(', ', 
      TRIM(SCDATA.SCACCT.GFIRST))) As
      Debtor,
    CONCAT((Select 
       VARCHAR_FORMAT(Min(TIMESTAMP_FORMAT(Cast(SCDATA.SCACCT.GPLDAT
       As Char(10)), 'YYYYMMDD')), 'MM/DD/YYYY') From SCDATA.SCACCT
       Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501), CONCAT(' 
       - ', (Select 
       VARCHAR_FORMAT(Max(TIMESTAMP_FORMAT(Cast(SCDATA.SCACCT.GPLDAT As
       Char(10)), 'YYYYMMDD')), 'MM/DD/YYYY') From SCDATA.SCACCT
       Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501))) As 
       dateRange
  From SCDATA.SCCLNT
    Left Outer Join SCDATA.SCACCT On 
      CDATA.SCACCT.GCLNT = SCDATA.SCCLNT.CCLNT
  Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501 And
    SCDATA.SCCLNT.CLTGRP Like '######'
  </code></pre>
  
  <p>This query works fine when the SCACCT table returns values but I need at least a single row from SCCLNT to populate client details in the report.  The report software we are using (DBExtra.net) does not support multiple queries per report so it all has to be bundled into a single query.</p>
  
  <p>Unfortunately beyond basic queries SQL is not one of my strong suits so all help will be appreciated.  Thank you.</p>