I have two SQL statements. The first one works great, and gives me the information I need. The second one I am really struggling with. It pulls information from the first one. I will try and explain my problem as well as I can.

Here is the first statement:

select course.courseid, count(studentcourse.courseid) as stdcount, courseinstruc.instructor;
from course left join studentcourse on course.courseid = studentcourse.courseid;
left join courseinstruc on studentcourse.instructorid = courseinstruc.instructor;
into cursor tempcourse group by course.courseid, courseinstruc.instructor

An example output looks like this

courseid | stdcount | instructor

CJP165 | 1 | CJ01
CJP165 | 2 | CJ05
CJP166 | 0 | NULL

Thats exactly what I want that SQL statement to do. The second one I want to select the stdcount from that cursor. I have simplified this select statement just so its easier to follow. This isn't everything I want but this is the data I am having problems getting.

select course.courseid, courseinstruc.instructor, tempcourse.stdcount;
from course left join courseinstruc on course.courseid = courseinstruc.courseid,;
tempcourse;
where course.courseid = tempcourse.courseid;
into cursor temporary


The output looks something like this

courseid | instructor | stdcount

CJP165 | CJ01 | 1
CJP165 | CJ01 | 2
CJP165 | CJ05 | 1
CJP165 | CJ05 | 2

I want the instructor listed only once with the correct stdcount. I know it seems like I should calculate the stdcount in the second one but I use the stdcount from the first cursor to do calculations with it in the second cursor.

I don't know if this makes alot of sense but I need help.

Thanks,
Tyler