Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-11 : 06:16:08
|
| Borom Simon writes " Query to accept any courses.Posted: 07-07-2005 04:33 AM Hi All, scractching my head on this one. To illustrate the problem, let’s consider example: We have some students kept in database table called tblStuddent tblStudent SID NAME S1 Sk S2 Jo S3 Be S4 Ma S5 Sa We also have some courses kept in table called tblCourse tblCourse CID COURSE C1 Latin C2 Chemistry C3 Bilolgy And, as it is many-to-many relationship, the enrolment is kept in a joint table called tblStudentCourse: tblStudentCourse SID CID S1 C1 S1 C2 S1 C3 S2 C1 S3 C3 S4 C1 S4 C2 S5 C2 Here is a resulting enrolment QueryAll NAME COURSE Be Biology Jo Latin Ma Chemistry Ma Latin Sa Chemistry Sk Biology Sk Chemistry Sk Latin Now I am trying to find only those students who registered for BOTH, 'Latin' and 'Chemistry'. In our case, the correct query should retrieve only Skevoulis and Ma. Next, what if I want to find the student who registered for three courses: Biology, Chemistry and Latin. In our case, it is only one student: Sk. And so on. In other words, i am trying to create a query that would accept any number or courses and retrieve students who registered for all of them. " |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-11 : 06:31:51
|
To find those with Chemistry and Latin, Try thisSelect SID,CID from tblStudentCourse Twhere exists (select SID from tblStudentCourse where CID='C1' and SID=T.SID) andexists (select SID from tblStudentCourse where CID='C2' and SID=T.SID) MadhivananFailing to plan is Planning to fail |
 |
|
|
|
|
|
Db Sql Query