SQL Inner join where record does not exist

Question

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

To return rows/columns where join criteria doesn't exist, you need to use an OUTER JOIN, or UNION of whatever combination of queries gets the appropriate records with NULL placeholders. — OMG Ponies
– OMG Ponies, Commented Jan 26, 2013 at 2:07
visual explanation of joins codinghorror.com/blog/2007/10/… — xQbert
– xQbert, Commented Jan 26, 2013 at 2:16

BellevueBob · Accepted Answer · 2013-01-26 02:08:54Z

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

Collectives™ on Stack Overflow

SQL Inner join where record does not exist

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related