-1

I have two tables class_tbl (id,class_name) and student_tbl(id,class_id,student_name).

I want to display all class in a table format and want to show number of students in each class.

How to do this in one mysql query?

James Z
  • 12,209
  • 10
  • 24
  • 44
Jahangir Khattak
  • 3
  • 2
  • Search for mysql join 2 tables. – P.Salmon Mar 06 '18 at 11:25
  • 2
    Your student table design implies that a student can take only one class. Is that what you really want? – Honeyboy Wilson Mar 06 '18 at 13:00
  • No, in my case, one student can take many class and ofcourse one class have many students, so in these two tables there is many-to-many relationship, I just want to run a query that give me the list of all classes name and number of students belong to each class. But I want to do this by one query, how I will do this? I need a query for this along with little explaination. thanks Honeyboy Wilson – Jahangir Khattak Mar 08 '18 at 09:37
  • You need a linking table to establish that many-to-many relationship. Class_id does not belong in the students table. You need a studentsclasses table with a class_id and a student_id. – Honeyboy Wilson Mar 08 '18 at 21:10

2 Answers2

0

Use this: SELECT class_name, count(student_tbl.id) as number_of_students FROM class_tbl c INNER JOIN student_tbl s ON c.id=s.class_id GROUP BY class_id;

vpalade
  • 1,427
  • 1
  • 16
  • 20
0

You have provided very little information in your question. Please take some time to read some of the question on stackoverflow which are closed / answered / up-voted and spend some time thinking about why they have merited such action.

Assuming that your "student_tbl" actually decomposes the N:M relationship between students and classes (otherwise, as Honeyboy Wilson says, it is impossible to resolve the query) all you need is a count of records in the table grouped by class_id. If you want to add the name of the class then its just a join. This is remedial level SQL and already covered in other answers.

symcbean
  • 47,736
  • 6
  • 59
  • 94