2

I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query:

SELECT H.STUDENT_CODE,
       H.FIRSTNAME,
       H.LASTNAME,
       H.PROGRAM,
       H.TEACHER_CODE,
       H.COUNTRY_CODE,
       AC.COUNTRY_NAME,
       P.PROGRAM_NAME,
       T.TEACHER_NAME
    FROM HTUD H,
         PROGRAM P,
         TEACHER T,
         AP_COUNTRY AC
    WHERE H.FIRSTNAME LIKE '%ark%'
        AND H.PROGRAM = P.PROGRAM
        AND H.TEACHER_CODE = T.TEACHER_CODE
        AND H.COUNTRY_CODE = AC.COUNTRY_CODE
    ORDER BY H.STUDENT_CODE ASC 

These two columns H.TEACHER_CODE and H.COUNTRY_CODE have some empty values. If I run the query above, 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 wether 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?

5

You want to make an outer join. In Oracle 8i one has to use the (+) notation.

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H,
     PROGRAM P,
     TEACHER T,
     AP_COUNTRY AC
WHERE H.FIRSTNAME LIKE '%ark%'
    AND H.PROGRAM = P.PROGRAM
    AND H.TEACHER_CODE = T.TEACHER_CODE(+)
    AND H.COUNTRY_CODE = AC.COUNTRY_CODE(+)
ORDER BY H.STUDENT_CODE ASC 

Starting with Oracle 10g xou can use the ANSI join syntax,too

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H JOIN PROGRAM P ON (H.PROGRAM = P.PROGRAM)
    LEFT OUTER JOIN TEACHER T ON (H.TEACHER_CODE = T.TEACHER_CODE)
    LEFT OUTER JOIN AP_COUNTRY AC ON (H.COUNTRY_CODE = AC.COUNTRY_CODE)
WHERE H.FIRSTNAME LIKE '%ark%'
ORDER BY H.STUDENT_CODE ASC 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.