Re: Need help with select statement

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.

Since there are only 2 states in the column completed_sw, it's easy:

select student_id, class_subject, min(completed_sw) as completed   from assignment
  group by student_id, class_subject;

If there are more than 2 states, some trick is needed:

select student_id, class_subject,

    decode(sum(decode(completed_sw, 'Y', 1, 0)), count(*), 'Y', 'N') as completed   from assignment
  group by student_id, class_subject; Received on Thu Nov 25 1999 - 09:49:07 CST