I am using MariaDB,in my sample database I am having two tables department and emp.
I want to display all the department having less than 50 employees.
In this case it should display all the department names.
select count(a.deptId),b.deptname
from emp as a
right join department as b on a.deptId = b.deptId
group by a.deptId
having count(a.deptId) <= 50 ;
I tried above mentioned query, it is returning correct count for the department but it is leaving the last deptname operations mysteriously.
Can anybody explain me why it is happening?
My understanding : As per right join the right side table's all row will be fetched and matched with the other table. If matches found result will be displayed otherwise NULL will be printed.
+--------+-------------+
| deptId | deptname |
+--------+-------------+
| 501 | HR |
| 502 | Accounts |
| 503 | Development |
| 504 | Operations |
+--------+-------------+
+--------+--------+------+-------+--------+
| emp_id | salary | name | bonus | deptId |
+--------+--------+------+-------+--------+
| 1 | 850 | NULL | 10 | 501 |
| 2 | 920 | NULL | NULL | 502 |
| 3 | 550 | NULL | NULL | 501 |
| 4 | 450 | NULL | 5 | 502 |
| 5 | 800 | NULL | NULL | 501 |
| 6 | 920 | NULL | NULL | 502 |
| 7 | 155 | NULL | NULL | 501 |
| 8 | 50 | NULL | 20 | 501 |
+--------+--------+------+-------+--------+