Level 30
let's try this query
SELECT COUNT(abs.id) as AbsenceCount , dept.deptStringName
FROM Absences abs
JOIN Users u on u.id = abs.user_id
JOIN Departments dept on dept.id= u.dept_id
group by dept.deptStringName
Jun 2, 2017
3
Level 2
How to make two inner joins with a count in them?
I have three tables:
- Users (id, dept_id)
- Departments (id, deptStringName)
- Absences (id, user_id)
I am trying to figure out Absences per Department. I know I need two inner joins, but at the moment I am confused about how to insert a count in two inner joins using the Laravel Query Builder syntax.
The eventual result could be like:
+-------+---+
| DeptA | 3 |
| DeptF | 7 |
| DeptH | 3 |
| DeptT | 7 |
| DeptZ | 5 |
+-------+---+
Level 2
This works, thank you! Could you please explain it?
Level 30
ok @omrakhurs
1- you have three tables - absences , departments , users linked via foreign keys
2- we start from the absences because we have user_id
3- we join it with users
4- we join the users with departments because we have dept_id in the users table
5- we group them by departmentName after selecting the count of each absence per department.
1 like
Please or to participate in this conversation.