Using Field Value in Correlated Sub Query within a Case Statement

Question

I am attempting to run a correlated subquery (instead of a join), taking the field of the outer query (jobs.employee) and to look that up in the employee_team table to return their Team (e.Team)

 select jobs.client_id, jobs.employee,
        Employee_Team = (select distinct e.Team from employee_teams as e where e.employee = jobs.employee)
        from jobs

However I am clearly doing something wrong as this returns the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I.e. i *think* it is returning all unique types of teams that exist in the e.Team field.

Where am i going wrong?

Answer 1

An employee can be on more than one team. Use join:

select j.client_id, j.employee, e.Team
from jobs j join
     employee_teams et
     on et.employee = j.employee;

Or to combine the results on one row, use aggregation. Your error message looks like a SQL Server error message. In that database, you would use string_agg():

select j.client_id, j.employee, string_agg(e.Team, ', ') as teams
from jobs j join
     employee_teams et
     on et.employee = j.employee
group by j.client_id, j.employee;

Answer 2

If you only expect a one to one relationship you could add a TOP 1 :

select jobs.client_id, jobs.employee,
        Employee_Team = (select TOP 1 e.Team from employee_teams as e where e.employee = jobs.employee)
        from jobs

If there is a one to many relationship you will have to use a JOIN.