CODEWITHSUNDEEP
mysql
Mohamad
Mohamad

Reputation: 35349

Syntax error on same-table join in MySQL

I want to get a manager and a list of employees that work for that manager in the same query, as such:

record 1: manager.name, manager.title
record 2: employee.name, employee.title
record 3: employee.name, employee.title
record 4: employee.name, employee.title

The information is all stored in the same table. I tried the following query, but it gives me a syntax error "1064":

SELECT employees.id, employees.name, employees.title, employees.managerId, managers.id, managers.title, managers.name
FROM employees
FULL OUTER JOIN employees AS managers ON employees.id = managers.managerId
WHERE employees.id = '1'

Now I know this will work if I use left join but that doesn't seem efficient. The manager's name gets listed for each single employee row. I want it to be null. I know the manager's name already because it was the first record I asked for.

Any ideas where I'm going wrong?

Upvotes: 0

Views: 95

Answers (2)

Thilo
Thilo

Reputation: 17735

Use a union:

SELECT id, name, title FROM employees WHERE id = 1
UNION
SELECT id, name, title FROM employees WHERE managerId = 1

The first row is your manager, the others are his employees.

Upvotes: 2

David
David

Reputation: 16120

Why not just 

SELECT employees.id, employees.name, employees.title
WHERE employees.id = [manager ID]
OR employees.managerId = [manager ID]

?

That should produce the expected results you stated.

Upvotes: 2

Related Questions