This discussion is archived
3 Replies Latest reply: Jan 6, 2013 10:23 PM by Hjava RSS

left out join

Hjava Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    basically, just return everything from ps_acad_prog table. do I right?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points