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)