CODEWITHSUNDEEP
mysqlsqlrequest
Khrisna Gunanasurya
Khrisna Gunanasurya

Reputation: 745

Get specific COUNT value with same ID column

I have this query

SELECT * 
FROM classroom 
WHERE capacity > (SELECT COUNT(class_history.*) 
                  FROM classroom 
                  RIGHT JOIN class_history ON classroom.id_class = class_history.id_class 
                  GROUP BY class_history.id_class) 
ORDER BY id_class DESC

And I want to get the classroom capacity from counting class_history record, that same with the classroom id, can anyone help me with this query?

So I can get the return like this

classroom 1 - capacity  (0/20)
classroom 2 - capacity  (2/20)
classroom 3 - capacity  (9/10)

Here the screenshot

Table classroom classroom

Table class_history class_history

NOTE : if the capacity is full then it didnt appear , if not full then it will appear no matter if its still 0

Upvotes: 0

Views: 74

Answers (1)

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

use LEFT JOIN, it should solve your issue

SELECT classroom.id_class, COUNT(class_history.id_class), capacity 
FROM classroom LEFT JOIN class_history
ON classroom.id_class = class_history.id_class
GROUP BY classroom.id_class
HAVING COUNT(class_history.id_class) < capacity

Upvotes: 1

Related Questions