Re: Need help with select statement

Actually this should be done using a student and an assignment table like this
select *
from student s
where not exists
(select 'x'
  from assignment a
  where a.student_id = s.student_id
  and a.completed = 'N')

Variants with assignment only will return weird statements.

Hth,

--
Sybrand Bakker, Oracle DBA
Pornpira Vachareeyanukul <pornpira_at_beta.tricity.wsu.edu> wrote in message news:Pine.OSF.4.10.9911231511020.32202-100000_at_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 - 23:42:28 CST