1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle optional select value

Discussion in 'SQL PL/SQL' started by narazana, May 12, 2014.

  1. narazana

    narazana Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query:

    Please see the attachment for the SQL. I wrote them here but could not submit. It always says "Oops! your Post seems to be a SPAM!..." Can anyone check why I cannot submit such query? :confused:

    These two columns H.TEACHER_CODE and H.COUNTRY_CODE have some empty values. If I run the query below, it returns only students that have data in these two columns H.TEACHER_CODE and H.COUNTRY_CODE.

    However, I want to select all students whether they have data in these two columns or not. If they have data in H.TEACHER_CODE and H.COUNTRY_CODE column, then return Teacher and Country also, if they don't have, just return students without Teacher or Country.

    So, how do I select all students with or without Teacher or Country?
     

    Attached Files:

  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Use outer joins (+)
     
    narazana likes this.
  3. narazana

    narazana Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Yes, it works:

    AND H.TEACHER_CODE = T.TEACHER_CODE(+)
    AND H.COUNTRY_CODE = AC.COUNTRY_CODE(+)