3 Replies Latest reply: Jan 7, 2013 12:23 AM by Hjava RSS

    left out join

    Hjava
      I got lost on the folowing left join
      ((ps_acad_prog b LEFT OUTER JOIN ps_adm_app_car_seq a ON a.emplid =b.emplid
      AND a.acad_career = b.acad_career
      and A.STDNT_CAR_NBR_SR = B.STDNT_CAR_NBR
      AND a.adm_appl_nbr =b.adm_appl_nbr) LEFT OUTER JOIN ps_adm_appl_data a ON a.emplid = a.emplid
      AND a.acad_career = a.acad_career
      and a.STDNT_CAR_NBR = A.STDNT_CAR_NBR
      AND a.adm_appl_nbr = a.adm_appl_nbr)


      I need to add one join table PS_Z_APPL_PROg on the following condition
      from PS_APPL_PROG a
      inner join PS_ADM_APPL_DATA B on a.EMPLID=B.EMPLID
      inner join PS_Z_ADM_APPL_PROG C on C.Z_ADM_APPL_NBR=B.EXT_ADM_APPL_NBR
      where a.ACAD_CAREER=B.ACAD_CAREER
      and a.ACAD_PROG=c.ACAD_PROG
      and a.EMPLID=B.EMPLID
      any suggestion?
        • 1. Re: left out join
          sb92075
          Hjava wrote:
          I got lost on the folowing left join
          ((ps_acad_prog b LEFT OUTER JOIN ps_adm_app_car_seq a ON a.emplid =b.emplid
          AND a.acad_career = b.acad_career
          and A.STDNT_CAR_NBR_SR = B.STDNT_CAR_NBR
          AND a.adm_appl_nbr =b.adm_appl_nbr) LEFT OUTER JOIN ps_adm_appl_data a ON a.emplid = a.emplid
          AND a.acad_career = a.acad_career
          and a.STDNT_CAR_NBR = A.STDNT_CAR_NBR
          AND a.adm_appl_nbr = a.adm_appl_nbr)


          I need to add one join table PS_Z_APPL_PROg on the following condition
          from PS_APPL_PROG a
          inner join PS_ADM_APPL_DATA B on a.EMPLID=B.EMPLID
          inner join PS_Z_ADM_APPL_PROG C on C.Z_ADM_APPL_NBR=B.EXT_ADM_APPL_NBR
          where a.ACAD_CAREER=B.ACAD_CAREER
          and a.ACAD_PROG=c.ACAD_PROG
          and a.EMPLID=B.EMPLID
          any suggestion?
          since I don't have your tables or data & do not know the expected or desired results,
          I can not begin to offer any suggestion


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: left out join
            IckyIckyChiMoon
            I think this is a great example of how neatly formatting your code can help you find the problems. Notice the table aliases on the second left-join.

            You mention an additional table you wish to join and include a where-clause with some additional join conditions. Make sure you know the difference between a join-clause condition and a where-clause condition -- especially when you are using outer joins. If your joins and where-clause are not properly specified, you could easily be getting the wrong results.
               from  ps_acad_prog        b 
                  LEFT OUTER JOIN 
                  ps_adm_app_car_seq  a  on ( a.emplid           = b.emplid         and 
                                              a.acad_career      = b.acad_career    and 
                                              a.stdnt_car_nbr_sr = b.stdnt_car_nbr  and 
                                              a.adm_appl_nbr     = b.adm_appl_nbr     )
                  LEFT OUTER JOIN  
                  ps_adm_appl_data    a  on ( a.emplid           = a.emplid         and 
                                              a.acad_career      = a.acad_career    and 
                                              a.stdnt_car_nbr    = a.stdnt_car_nbr  and 
                                              a.adm_appl_nbr     = a.adm_appl_nbr     )
                  INNER JOIN
                  ps_z_adm_appl_prog  c  on ( c.z_adm_appl_nbr   = b.ext_adm_appl_nbr )
            • 3. Re: left out join
              Hjava
              basically, just return everything from ps_acad_prog table. do I right?