CODEWITHSUNDEEP
sqlsql-servert-sql
jbq
jbq

Reputation: 191

SQL Inner join where record does not exist

I currently have 2 tables that I'm querying using the INNER JOIN clause. My first table labeled departments is fairly simple, containing only 3 fields - deptID, deptName, and companyID. My second table labeled departmentMemberships is even simpler, containing only 2 field - deptID and employeeID. My current SQL statement queries both of these tables and returns all of the deptID's, deptName's and the number of employees in each department. My query will not return a department if there aren't any employees enlisted in the aforementioned department. This is my current query.

`SELECT departments.deptID, departments.deptName,
COUNT(departmentMemberships.deptID) AS employeeCount
FROM departmentMemberships
INNER JOIN departments
ON departmentMemberships.deptID = departments.deptID
WHERE departments.companyID = 1
GROUP BY departments.deptID, departments.deptName
ORDER BY departments.deptName ASC;`

Excuse my inexperience with TSQL, but how could I alter this query to return all departments, including the ones with no employees enrolled?

Thank you very much

Upvotes: 2

Views: 16730

Answers (1)

BellevueBob
BellevueBob

Reputation: 9618

As written, change your INNER JOIN to a RIGHT OUTER JOIN. But I prefer LEFT JOIN myself; it's easier to follow. I.e.:

SELECT departments.deptID
     , departments.deptName
     , COUNT(departmentMemberships.deptID) AS employeeCount
FROM departments
LEFT OUTER JOIN departmentMemberships
ON departmentMemberships.deptID = departments.deptID
WHERE departments.companyID = 1
GROUP BY departments.deptID, departments.deptName
ORDER BY departments.deptName ASC

Upvotes: 7

Related Questions