General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Thanks so far everyone,
I have got the results I want but now I want to join them. When I do two different select statements I get the right results. But when I join them I don't get the combined student counts for the two select statements
select courseprogram.programid, studentcourse.courseid, &groupchoice as mygroup,;
instructor.ifname, count(studentcourse.instructorid) as numstud, studentcourse.instructorid,;
nvl(program.program, " ") as program;
from studentcourse left join instructor on studentcourse.instructorid = instructor.instructorid,;
courseprogram left join program on courseprogram.programid = program.programid;
where courseprogram.courseid = studentcourse.courseid;
and studentcourse.startdate >= {12/01/1999};
and (studentcourse.dropdate != {};
and (month(studentcourse.dropdate) - month(studentcourse.startdate) >= 1;
or year(studentcourse.dropdate) - year(studentcourse.startdate) >= 1));
and studentcourse.iscomplete = .F.;
group by mygroup, studentcourse.courseid, studentcourse.instructorid;
into cursor cltemp
In this select statement for the course ID RSP120 I get 1 student. Which is correct
**********************************************************
select courseprogram.programid, studentcourse.courseid, &groupchoice as mygroup,;
instructor.ifname, count(studentcourse.instructorid) as numstud, studentcourse.instructorid,;
nvl(program.program, " ") as program;
from studentcourse left join instructor on studentcourse.instructorid = instructor.instructorid,;
courseprogram left join program on courseprogram.programid = program.programid;
where courseprogram.courseid = studentcourse.courseid;
and studentcourse.startdate >= {12/01/1999};
and studentcourse.dropdate == {};
and studentcourse.iscomplete = .F.;
group by mygroup, studentcourse.courseid, studentcourse.instructorid;
into cursor clbudgetnew
For this one I get 2 students for RSP120 which is correct. So I thought by using a union select I would get a total of 3 students which I am looking for as well as everything else selected. I know understand that this might not be the way to do this. So if anyone could give some advice that would be cool.
Thanks,
Tyler
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only