Jump to content

Many to Many Tables


Gauls

Recommended Posts

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

Try this....SELECT    student.studentnameFROM        studentWHERE    (NOT (student.studentid =                          (SELECT    Mark.StudentID                            FROM          Mark                            WHERE      Mark.Lesson id = 'L2')))

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...