The query logic is correct and I have tried it in Oracle and it's running fine, but I'm getting an error when running this in MySQL

Question

SELECT Personal_ID,FirstName,LastName
FROM Person
WHERE Personal_ID in
(
SELECT EmployeeID as Personal_ID
FROM
(
SELECT EmployeeID,Count(Distinct(Department.DEPARTMENTID)) as CountDepartment
FROM Shift inner join Department on Department.DEPARTMENTID=Shift.DEPARTMENTID 
GROUP BY EmployeeID
)
WHERE CountDepartment=(select count(*) from department )
);

This is the error I'm getting:

ERROR 1248 (42000): Every derived table must have its own alias

pity, I was preparing an answer. In MySQL every derived table requires an alias, but Oracle does not. However if you want to write portable code from Oracle to MySQL take care how you use the alias. Don't use **as alias_name** in Oracle (leave out the **as**!!), then it will work in both environments. — Paul Maxwell, Nov 21 '17 at 08:43

score 1 · Answer 1 · answered Nov 21 '17 at 08:09

Add a as temp_table before the final WHERE clause where you are returning EmployeeID as Personal_ID. It's one of the requirements of MySql when it comes to derived tables.

SELECT Personal_ID,FirstName,LastName
FROM Person
WHERE Personal_ID in
(
SELECT EmployeeID as Personal_ID
FROM
(
SELECT EmployeeID,Count(Distinct(Department.DEPARTMENTID)) as CountDepartment
FROM Shift inner join Department on Department.DEPARTMENTID=Shift.DEPARTMENTID 
GROUP BY EmployeeID
) as temp_table           -- try adding this
WHERE CountDepartment=(select count(*) from department )
);

That worked, thanks a lot – Michael Mckenzie Nov 21 '17 at 09:37 — Michael Mckenzie, Nov 21 '17 at 09:37

score 0 · Answer 2 · answered Nov 21 '17 at 08:16

You should use alias for each subselect .. and you could also avoid repetead subquery

  SELECT p.Personal_ID,p.FirstName,p.LastName, count(*) 
  FROM Person p
  INNER JOIN (
      SELECT EmployeeID,Count(Distinct(Department.DEPARTMENTID)) as CountDepartment
      FROM Shift 
      inner join Department on Department.DEPARTMENTID=Shift.DEPARTMENTID 
      GROUP BY EmployeeID
  ) t on t.EmployeeID = p.Personal_ID 
  group by 
  having  count(*) = t.CountDepartment

The query logic is correct and I have tried it in Oracle and it's running fine, but I'm getting an error when running this in MySQL

2 Answers2