INNER JOIN with distinct columns

Question

I'm able to code a select statement that performs my query. However, I'm looking for the "DepartmentID" column displayed to only show unique values.

I'm attempting to use both "UNIQUE" and "DISTINCT" in various places but I'm unsuccessful. Here is the sample table data:

Output shows all "DepartmentID" entries, but I only want entries with unique values to be displayed.

Answer 1

If by "unique" values, you mean departments with exactly one employee, then use aggregation:

SELECT d.departmentid, d.departmentname, 
       MAX(e.FirstName || ' ' || e.LastName) AS Chair
FROM Employees e JOIN
     Departments d
     ON e.departmentid = d.departmentid 
GROUP BY d.departmentid, d.departmentname
HAVING COUNT(*) = 1
ORDER BY d.departmentid;

EDIT:

Based on your updated question, you don't need aggregation at all. Just the correct JOIN conditions:

SELECT d.departmentid, d.departmentname, 
       (e.FirstName || ' ' || e.LastName) AS Chair
FROM Employees e JOIN
     Departments d
     ON e.employeeid = d.departmentchair 
ORDER BY d.departmentid;