Re: Need help with select statement

As I interpret it, you want to group by student_id and class_subject, and print an "N" if the student has not completed all assignments for a subject, or print a "Y" if the student has completed all assignments for a subject. You do not want to print completed_sw for each assignment_no.

Assuming this, here is a simple solution in SQL*Plus:

COL dummy NOPRINT

SELECT
  MIN(student_id) STUDENT_ID,
  MIN(class_subject) CLASS_SUBJECT,
  student_id||class_subject DUMMY,
  MIN(completed_sw)
FROM assignment
GROUP BY (student_id||class_subject);

This assumes that completed_sw will always be 'N' or 'Y' (and not null) for each assignment_no.

HTH,
Larry

In article
<Pine.OSF.4.10.9911231511020.32202-100000_at_beta.tricity.wsu.edu>,   Pornpira Vachareeyanukul <pornpira_at_beta.tricity.wsu.edu> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 24 1999 - 09:31:11 CST