title: mapping many to many items
body: <p>I'm using database that looks like this the following:</p>
  
  <pre><code>CREATE TABLE Students(
      [StudentId] [int],
      ...,
      [MajorId] [int]
  );
  CREATE TABLE Majors(
      [MajorId] [int],
      ...,
      [MajorDes] [varchar](20)
  );
  CREATE TABLE CourseRequiredForEachMajor(
      [MajorId] [int],
      ...,
      [CourseTitle] [varchar](20)
  );
  
  CREATE TABLE CoursesCompletedByStudents(
      [StudentId] [int],
      ...,
      [CourseTitle][varchar] (20)
  );
  INSERT INTO Majors ([MajorId] , .. , [MajorDes]) VALUES (1, ... . 'IT');
  
  INSERT INTO Students ([StudentId], .. , [MajorId]) VALUES (1, ... . 1);
  
  INSERT INTO CourseRequiredForEachMajor ([MajorId], .. , [CourseTitle]) VALUES (1, ... . 'IT101');
  INSERT INTO CourseRequiredForEachMajor ([MajorId], .. , [CourseTitle]) VALUES (1,...,'IT302');
  INSERT INTO CourseRequiredForEachMajor ([MajorId], .. , [CourseTitle]) VALUES(1,...,'IT321');
  
  INSERT INTO CoursesCompletedByStudents ([StudentId], .. , [CourseTitle]) VALUES (1, ... . 'IT101');
  INSERT INTO CoursesCompletedByStudents ([StudentId], .. , [CourseTitle]) VALUES (1,...,'IT302');
  INSERT INTO CoursesCompletedByStudents ([StudentId], .. , [CourseTitle]) VALUES(2,...,'IT321');
  </code></pre>
  
  <p>I'm trying to list the missing courses for each student in order to complete the all the requirements for each major ( e.g. in the image above, I want to say that studentId# 1 need to take course IT321 in order to complete all requirement for majorId #1 ). 
  Also is there anyway that I can do to show percentage of completion ? ( e.g. studentId#1 completed two courses ( 66.6% ) out of 3 courses that are required by the major ). what is the way to do the calculations of this.</p>
  
  <p>I really have no idea how to solve this, but this is my attempt:</p>
  
  <pre><code>Select CoursesCompletedByStudents.CourseTitle from Students,MajorsCourseRequiredForEachMajor,CoursesCompletedByStudents
   where Students.MajorId= Major.MajorId and Studetns.StudentId = CoursesCompletedByStudents.[StudentId]
   and CourseRequiredForEachMajor.[CourseTitle]=CoursesCompletedByStudents.[CourseTitle]
  </code></pre>