Gauls Posted June 29, 2006 Share Posted June 29, 2006 I have a query to make with three tables, they are as followsTable names ColumnsLesson LessonID(PK), Lesson NameStudent StudentID (PK), Student Name Mark MarkID, Mark, StudentID (FK),LessonID (FK)The Combination of StudentID and LessonID are unique in the Marks TableExample :LessonLessonID , Lesson Name1. L1 Lesson12. L2 Lesson23. L3 Lesson3StudentStudentID , Student Name1. S1 Student12. S2 Student23. S3 Student34. S4 Student4 MarkMarkID, Mark , StudentID, LessonID1.M1, Mark1 S1 L12.M2, Mark1 S2 L13.M3, Mark1 S3 L14.M1, Mark1 S1 L25.M2, Mark1 S2 L26.M3, Mark1 S3 L3Result Should be If the Selected Lesson is Lesson2 (L2)Student3 (S3)Student4 (S4)my query is Get all the Students that are not in the selected Lesson The problem is to get all the students Name from the students table that are not listed under lesson2 in the marks table All in one single query Link to comment Share on other sites More sharing options...
pulpfiction Posted June 29, 2006 Share Posted June 29, 2006 Try this....SELECT student.studentnameFROM studentWHERE (NOT (student.studentid = (SELECT Mark.StudentID FROM Mark WHERE Mark.Lesson id = 'L2'))) Link to comment Share on other sites More sharing options...
Gauls Posted June 29, 2006 Author Share Posted June 29, 2006 Try this....SELECT student.studentnameFROM studentWHERE (NOT (student.studentid = (SELECT Mark.StudentID FROM Mark WHERE Mark.Lesson id = 'L2')))<{POST_SNAPBACK}> Thanks pulpfiction But this query gives an error as the result returns more then one value so can't use subqueries with = != operatorsCan we use joins? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now