Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Need help with select statement
Hi there,
I need some help with the select statement. I've tried everything and stil
haven't come up with the answer.
I have a table called ASSIGNMENT with
student_id,
class_subject,
assignment_no,
completed_sw => value can be Y or N
Each student can have 0 or more assignments for each class subect. I want to find out for each student if they completed all their class assignments or not.
For example
Student A has
assignment 1- not completed, assignment 2- not completed, assignment 3- completed
Student A also has
assignment 1- completed,
assignment 2- completed
for class subject=math
I want the result to be like
student class subject completed
------ ------------ -----
A history N A math Y
I've come upt with the select statement that finds all students who have not completed their assignments.
Select distinct student_id, class_subject, 'N'
from assignment a
where 'N' in
(select distinct p.completed_sw from assignment p where a.student_id = p.student_id and a.class_subject = p.class_subject )
I still need to find out which students have completed all the assignments.
Any tips or help would be appreciated.
Thank you,
Mimi
Received on Tue Nov 23 1999 - 17:17:54 CST