CODEWITHSUNDEEP
sqloracle-databasenullleft-join
Manual
Manual

Reputation: 1687

Row not returning NULL

I am trying to select students along with a number of enrolled each one of them has. So if the student hasn't enrolled at all in any courses I want to display 0 or none. Before I can do this I want the row to return me null, but it is not happening. It is giving me the right result except I don't have a row that is null. 

SELECT s.STUDENT_ID, z.COURSE_NO, e.ENROLL_DATE, COUNT(*) AS ENROLLED
FROM STUDENT s,ENROLLMENT e, SECTION z
WHERE s.STUDENT_ID = e.STUDENT_ID
AND e.SECTION_ID = z.SECTION_ID
AND s.PHONE LIKE '702%'
GROUP BY s.STUDENT_ID, z.COURSE_NO, e.ENROLL_DATE

So if it did return null for the students that haven't enrolled in any courses I wanted to do something like this

SELECT lt.STUDENT_ID,lt.FIRST_NAME, lt.LAST_NAME, NVL(COUNT(ENROLLED), 0) AS ENROLLED
FROM STUDENT lt
LEFT OUTER JOIN
(SELECT s.STUDENT_ID, z.COURSE_NO, e.ENROLL_DATE, COUNT(*) AS ENROLLED
FROM STUDENT s,ENROLLMENT e, SECTION z
WHERE s.STUDENT_ID = e.STUDENT_ID
AND e.SECTION_ID = z.SECTION_ID
AND s.PHONE LIKE '702%'
GROUP BY s.STUDENT_ID, z.COURSE_NO, e.ENROLL_DATE) rt
ON lt.STUDENT_ID = rt.STUDENT_ID
GROUP BY lt.STUDENT_ID,lt.FIRST_NAME, lt.LAST_NAME;

How do I get a row that returns null?

Upvotes: 0

Views: 81

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

Use a LEFT [OUTER] JOIN:

SELECT s.STUDENT_ID, z.COURSE_NO, e.ENROLL_DATE, COUNT(*) AS ENROLLED
FROM   STUDENT         s
LEFT   JOIN ENROLLMENT e ON e.STUDENT_ID = s.STUDENT_ID 
LEFT   JOIN SECTION    z ON z.SECTION_ID = e.SECTION_ID
WHERE  s.PHONE LIKE '702%'
GROUP  BY s.STUDENT_ID, z.COURSE_NO, e.ENROLL_DATE

Details in the language reference.

Upvotes: 3

Related Questions