Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Linking tables correctly?
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement