CODEWITHSUNDEEP
mysqljoingroup-bycount
jimmy118
jimmy118

Reputation: 323

Trying to use join, count and group by, but not working as I want

I am querying my database which is a company directory. The company directory has three separate tables; Staff, Departments and Locations.

Below is an image of what my table structure looks like: enter image description here

I am trying to query the database so that I can get the columns of department id, department name, location id, location name and also the number of staff members within that department. However, I also need it to list the department even if there are no staff members assigned to it just yet.

I am not sure what type of join to use, but I do know I must use a count and groupBy function.

I am still very new to SQL and am not exactly very knowledgeable just yet. I have tried one or two approaches but to no avail.

Here is my attempt, which does bring results as I wanted, but it excludes departments which have no staff members.

SELECT p.id, p.lastName, p.firstName, p.jobTitle, p.email, d.id, 
    d.name as department, l.name as location, COUNT(*) as totalStaff 
FROM personnel p 
LEFT JOIN department d ON (d.id = p.departmentID) 
LEFT JOIN location l ON (l.id = d.locationID) 
GROUP BY (d.id)

Is anyone able to assist?

Upvotes: 1

Views: 514

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94642

Starting from department and left joining the other 2 tables will ensure you always get all the dept info which I seem to remember was your original issue in the previous question

SELECT p.id, p.lastName, p.firstName, p.jobTitle, p.email, 
        d.id, d.name as department, 
        l.name as location, 
        COUNT(p.id) as totalStaff 
FROM department d
    LEFT JOIN personnel p ON (d.id = p.departmentID) 
    LEFT JOIN location l ON (l.id = d.locationID) 
GROUP BY (d.id)

Upvotes: 1

Related Questions