0

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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

1 Comment

That doesn't necessarily put the manager row first, though. If manager ID is zero or NULL for managers, appending ORDER BY managerId ASC might work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.