 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Wei
Guest
|
 Posted: Sat Aug 28, 2004 12:44 pm Post subject: Where am I wrong in this 3-table left join query? |
 |
|
I have tables student_course, completion, withdrawal.
The schema are similar: studentid string, courseid string
With completion containing all the completed courses, withdrawal containing all the withdrawed students. And student_course containing all current active student-course combination and completions and withdrawals.
Now I want to find out a student's current active courses, which is basically student_couser MINUS completion MINUS withdrawal.
My query is as follows:
select a.*
from std_course a left join completion b on (a.learnerid = b.learnerid and a.COURSECODE=b.COURSECODE) left join withdrawal c on (a.learnerid= c.learnerid and a.LEARNERID=c.COURSECODE)
where b.COURSECODE is null and c.COURSECODE is null;
Now this query only returns student_course MINUS completion. The withdrawal records are still there. How should I correct this query?
|
|
| Back to top |
|
 |
Wayne Niddery [TeamB]
Guest
|
|
| Back to top |
|
|
corey lawson
Guest
|
| Posted: Tue Oct 19, 2004 7:01 am Post subject: Re: Where am I wrong in this 3-table left join query? |
|
|
Wei wrote:
| Quote: | I have tables student_course, completion, withdrawal.
The schema are similar: studentid string, courseid string
With completion containing all the completed courses, withdrawal containing all the withdrawed students. And student_course containing all current active student-course combination and completions and withdrawals.
Now I want to find out a student's current active courses, which is basically student_couser MINUS completion MINUS withdrawal.
My query is as follows:
select a.*
from std_course a left join completion b on (a.learnerid = b.learnerid and a.COURSECODE=b.COURSECODE) left join withdrawal c on (a.learnerid= c.learnerid and a.LEARNERID=c.COURSECODE)
where b.COURSECODE is null and c.COURSECODE is null;
Now this query only returns student_course MINUS completion. The withdrawal records are still there. How should I correct this query?
|
Combine the two tables, Completion and Withdrawl, perhaps named
StudentCourseHistory. Indicate with two flags whether a given course has
been completed or withdrawn from from in this table. Then, selecting
active courses is simply selecting the records for a student that don't
have either of these two flags set.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
