title: Select or Join tatement for BIRT Report using SQL
body: <p>I am just getting back into writing SQL Select Statements and I am have an issue. I have an existing report that works perfectly. However, I was asked to add some fields in the result set. The filed is in a table with a foreign key relationship. I am trying to add it to the query and I am failing. I can write the select statement separately and it returns the right results. I just don't know how to add it to the existing statement. I have tried left joins, adding it to the select and where but the result set is wrong. I would like to add the following to return the info if available to the result set in a new column but not prevent other data if null.</p>
  
  <p>I would like to add:
      select sc.FACILITY_ID, ce.schd_id
      from PA_SCHED sc, PA_CPNT_EVTHST ce
      where sc.SCHD_ID = ce.schd_id</p>
  
  <p>To the following:</p>
  
  <pre><code>select s.stud_id as studID, s.lname as lastName, s.fname as firstName, s.mi
  as middleName,
     s.EMP_STAT_ID ,s.EMP_TYP_ID ,s.JL_ID ,s.JP_ID ,s.TARGET_JP_ID ,
     s.JOB_TITLE ,s.DMN_ID ,s.ORG_ID ,s.REGION_ID, s.CO_ID , DECODE
    (s.NOTACTIVE,'Y','N','N','Y') as active ,
     s.ADDR ,s.CITY ,s.STATE ,s.POSTAL ,s.CNTRY, s.SUPER , s.COACH_STUD_ID ,
     s.HIRE_DTE,s.TERM_DTE ,s.EMAIL_ADDR, s.RESUME_LOCN ,s.COMMENTS , 
     s.SHIPPING_NAME ,
     s.SHIPPING_CONTACT_NAME,  
     s.SHIPPING_ADDR ,s.SHIPPING_ADDR1 ,s.SHIPPING_CITY ,
     s.SHIPPING_STATE,
     s.SHIPPING_POSTAL ,s.SHIPPING_CNTRY ,s.SHIPPING_PHON_NUM,
     s.SHIPPING_FAX_NUM, 
     s.SHIPPING_EMAIL_ADDR ,s.STUD_PSWD ,s.PIN ,s.PIN_DATE,
     s.ENCRYPTED , s.HAS_ACCESS ,s.BILLING_NAME , s.BILLING_CONTACT_NAME ,
     s.BILLING_ADDR ,s.BILLING_ADDR1 ,s.BILLING_CITY ,s.BILLING_STATE ,
     s.BILLING_POSTAL,
     s.BILLING_CNTRY , 
     s.BILLING_PHON_NUM ,s.BILLING_FAX_NUM ,s.BILLING_EMAIL_ADDR ,
     s.SELF_REGISTRATION ,s.SELF_REGISTRATION_DATE, s.ACCESS_TO_ORG_FIN_ACT ,
     s.NOTIFY_DEV_PLAN_ITEM_ADD , s.NOTIFY_DEV_PLAN_ITEM_MOD ,
     s.NOTIFY_DEV_PLAN_ITEM_REMOVE ,
     s.NOTIFY_WHEN_SUB_ITEM_COMPLETE ,s.NOTIFY_WHEN_SUB_ITEM_FAILURE ,
     s.LOCKED ,s.PASSWORD_EXP_DATE,s.SECURITY_QUESTION ,
     s.SECURITY_ANSWER ,s.ROLE_ID ,s.IMAGE_ID ,s.GENDER ,s.PAST_SERVICE, 
     s.LST_UNLOCK_TSTMP,
     s.LST_UPD_USR, s.LST_UPD_TSTMP,s.MANAGE_SUB_SP, s.MANAGE_OWN_SP,
  
     e.cpnt_typ_id as cpntTypeID, e.cpnt_id as cpntID, e.rev_dte as revDate, 
     e.rev_num as revNum,
     e.schd_id as schedID, cp.cpnt_title as cpntDesc, e.grade,
     e.compl_dte as complDate, e.cmpl_stat_id as complStatID,
     cs.cmpl_stat_desc as complStatDesc, nvl(e.total_hrs, 0) as totalHrs,
     nvl(e.credit_hrs, 0) as creditHrs, nvl(e.contact_hrs, 0) as contactHrs,
     nvl(e.cpe_hrs, 0) as cpeHrs, nvl(oi.price, 0) as tuition,
     e.inst_name as instName, e.comments as eventComments, e.es_user_name as 
     esUserName,
     e.lst_upd_tstmp as lstTmsp,
     e.esig_meaning_code_id || '  ' ||  e.esig_meaning_code_desc as 
     esigMeaningCode,
     e.currency_code as currencyCode, cur.symbol as currencySymbol, 
     cur.description,
     cur.active, cur.is_default, cur.label_id
     ,s.jl_id
     ,s.job_title
     ,s.super
     ,s.hire_dte
     ,s.term_dte
     ,eeno.user_value
     ,co.user_value
  </code></pre>
  
  <p>from pa_student s, PA_CPNT_EVTHST e, PA_CMPL_STAT cs,
          pa_fin_order_item oi, pa_currency cur, pa_cpnt cp,</p>
  
  <pre><code>   (SELECT s.stud_id, f.user_value
  FROM PA_STUD_USER f, pa_student s
  WHERE s.stud_id = f.stud_id (+)
  AND f.col_num (+) = '10') eeno
  ,(SELECT s.stud_id, f.user_value
  FROM PA_STUD_USER f, pa_student s
  WHERE s.stud_id = f.stud_id (+)
  AND f.col_num (+) = '40') co
  
  where e.stud_id = s.stud_id
    and e.cmpl_stat_id = cs.cmpl_stat_id
    and e.currency_code = cur.currency_code(+)
    and e.order_item_id = oi.order_item_id(+)
    and cp.cpnt_typ_id(+) = e.cpnt_typ_id
    and cp.cpnt_id(+) = e.cpnt_id
    and cp.rev_dte(+) = e.rev_dte
    and s.stud_id = eeno.stud_id(+) 
    AND s.stud_id = co.stud_id(+)
  
  
  
  
   /**  and trunc(e.compl_dte) &gt;= [FromDate] 
     and not trunc(e.compl_dte) &gt; [ToDate]  
     and s.stud_id in [UserSearch]
     and e.cpnt_typ_id in [ItemTypeSearch]
     and [security:pa_student s] */
  </code></pre>
  
  <p>Thank you for your help.</p>