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

Please provide sample data and desired results, as tabular text. A non-working query is not enough. — GMB, Sep 26 '20 at 18:05
https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql — , Sep 26 '20 at 20:08
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, Sep 27 '20 at 02:46
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Sep 27 '20 at 02:47
This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Sep 27 '20 at 02:47
@AliceM. is there a reason that you accepted the latest answer? — forpas, Sep 29 '20 at 16:53

score 1 · Answer 1 · answered Sep 26 '20 at 18:52

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

score 1 · Accepted Answer · answered Sep 26 '20 at 19:02

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)

score 1 · Answer 3 · answered Sep 27 '20 at 00:56

1

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;

answered Sep 27 '20 at 00:56

Gordon Linoff

1,242,037
58
646
786

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

3 Answers3