mysqlsqljoinderived-table

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


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:

offices table

employees 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).


Solution

  • Current query has a number of issues:

    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)