Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Need help with select statement

Need help with select statement

From: Pornpira Vachareeyanukul <pornpira_at_beta.tricity.wsu.edu>
Date: Tue, 23 Nov 1999 15:17:54 -0800
Message-ID: <Pine.OSF.4.10.9911231511020.32202-100000@beta.tricity.wsu.edu>


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

for class subject=history

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US