title: Limiting records and putting restrictions
body: <p>What i want to do is, display all the rooms having capacity to accommodate students, In Rooms table it has a field "Capacity", in which Number of students that can be accommodate is specified,
  My idea was to select all the records of the students and check them if more then prescribed limit of student records are found against "capacity " Column  in any room, then App shouldn't allow user to insert the record, but i don't know how do i do it. I gotta accommodate students in rooms in a way that it must not accommodate a student in any room if the number of students accommodated in that room exceeds the available seats.</p>
  
  <p>what i tried:</p>
  
  <pre><code>select Student.StudentName,Student.RoomNumber,Rooms.RoomID 
  From Student 
  INNER JOIN  Rooms 
  ON Student.RoomNumber=Rooms.RoomId,
  </code></pre>
  
  <p>that's what i get and that's not what i need,
  <img src="https://i.stack.imgur.com/jbqEr.jpg" alt="enter image description here"></p>
  
  <p>so what i need is, jawad,hamid,asim are residents of room one which has the capacity to accomodate 3 students only, what  i adversly want is to display the rooms which do have capacity to accomodate new students, and if theere are more student records associated with a room record more then it's capacity then user must not be allowed to assign that room to a student.</p>