count number of member's class and show in grids-CodePudding

I have 3 tables:

member table :

|--------|---------------------|------------------|
|   id   |      f_name         |     l_name       |
|--------|---------------------|------------------|
|   1    |      matt           |        jim       |
|   2    |     david           |       joyce      |
|--------|---------------------|------------------|

mentor table :

|--------|---------------------|---------------|
|   id   |        fname        |      lname    |
|--------|---------------------|---------------|
|   1    |         mack        |       pet     |
|   2    |         larry       |       oley    |
|   3    |         roza        |       masti   |
|--------|---------------------|---------------|

class table :

|--------|-----------|----------|
|   id   |  classes  | Capacity |
|--------|-----------|----------|
|   1    |  Math     |  50      |
|   2    | chemistry |  40      |
|   3    |  Physics  |  60      |
|--------|-----------|----------|

I use this sql code to data in grids :

select mentor.id,mentor.fname   ' '   mentor.lname, class.classes, class.capacity, from mentor, class where mentor.id = class.id

now i want to show data in grid like this to calculate number of member of class

|--------|--------|----------|----------|--------------------|
|mentorid| mentor | classes  | Capacity |num of member class |
|--------|--------|----------|----------|--------------------|
|   1    |  mack  | Math     |  50      |    10              |
|   2    |  larry |chemistry |  40      |    12              |
|   3    |   roza |Physics   |  60      |     7              |
|--------|--------|----------|----------|--------------------|

The numbers 10, 12 and 7 are obtained from the following query : Select count(*) as num of member class from member where class = "Math". but i can't use this sub query in above query to get one result

The problem is how to calculate the number of class members and display it in the grid along with other information?

It should be noted that access is used as a database

CodePudding user response：

As i said you need to join a subquery, with the wanted data, and use always JOINS

select 
    mentor.id
    ,mentor.fname   ' '   mentor.lname
   , class.class
   , class.capacity
   , num_mem.[num of member class]
from mentor INNER JOIN class ON mentor.id = class.id
INNER JOIN (Select class,count(*) as [num of member class] from member GROUP BY class) num_mem ON class.class = num_mem.class