SQL query issue for count function on a subquery

Question

I have two tables

BatchDetails

BatchCode    MaxStd
------------------
B001          12
B002          14
B003          10

AdmissionBatch

Batch  Rollno
---------------
B001    1
B001    2
B002    3
B003    4
B003    5

I need the BatchCode of those batches are not have max students. I wrote a query but it is not working

select 
    batchCode, MaxStd, count(rollno)  
from 
    BatchDetails as a 
join 
    AdmissionBatch as b on a.batchcode = b.batch  
group by  
    batchcode, maxStd 
having 
    count(rollno) < maxSTD

This query is not working because if there is not student in a particular batch then the batch will not appear

I tried subquery as well but no help

Please help

Answer 1

You need a left join to keep the results even when there's no match :

select batchCode,MaxStd, count(rollno)
from BatchDetails as a 
left outer join AdmissionBatch  as b
 on a.batchcode=b.batch  
group by batchcode,maxStd 
having count(rollno)< maxSTD

Answer 2

Use left join instead of inner join.

select batchCode,MaxStd, count(rollno)  from BatchDetails as a 
LEFT JOIN AdmissionBatch  as b on a.batchcode=b.batch  
group by batchcode,maxStd 
having count(rollno)< maxSTD