Join data from two tables with distinct to select unique job titles

Question

I have two tables: employees and offices.

I want to create a view with all the columns from 'employees', but only two columns of 'offices'.

Also, I want to select only employees who have unique job titles. I'm trying to do it with the following code, but it returns the following error:

#1248 - Every derived table must have its own alias.

I'm using the following query:

SELECT employees.*, offices.officeCode, offices.phone 
FROM (
  SELECT DISTINCT employees.jobTitle
) 
JOIN offices ON employees.officeCode = offices.officeCode

offices table:

employees table:

Desired result:

employeeNumber|jobTitle|firstName|officeCode|city|state|country

including only the first 6 employees from the sample image (as 'Sales Rep' is a repeated jobTitle, the employees with it wouldn't be included).

Answer 1

Current query has a number of issues:

• As error indicates, derived table or subquery does not have an alias.
• Incomplete SELECT query in derived table without FROM clause: (SELECT distinct emplyees.jobtitle)
• Retrieving columns from a table not referenced in data sources of query (i.e., employees)

Therefore, consider joining the two tables with a count check on unique job title:

SELECT e.*, o.officeCode, o.phone 
FROM employees e
INNER JOIN offices o
   ON e.officeCode = o.officeCode
WHERE e.jobTitle IN
  (SELECT sub.jobTitle
   FROM employees sub
   GROUP BY sub.jobTitle
   HAVING COUNT(*) = 1)

Answer 2

Just use window functions:

SELECT e.*, o.officeCode, o.phone 
FROM (SELECT e.*, COUNT(*) OVER (PARTITION BY jobTitle) as job_cnt
      FROM employees e
     ) e JOIN
     offices o
     ON e.officeCode = o.officeCode
WHERE job_cnt = 1;

Answer 3

With this query:

SELECT e.* 
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees WHERE employeeNumber <> e.employeeNumber AND jobTitle = e.jobTitle)

you get all the employees with jobTitle that does not have any other employee.
So join it to offices:

SELECT e.employeeNumber, e.jobTitle, e.firstName, o.*
FROM (
  SELECT e.* 
  FROM employees e
  WHERE NOT EXISTS (SELECT 1 FROM employees WHERE employeeNumber <> e.employeeNumber AND jobTitle = e.jobTitle) 
) e INNER JOIN offices o
ON e.officeCode = o.officeCode