LEFT JOIN with a subquery (?)

[Chip]

I cannot figure out how to put the following into practice - any help
is appreciated.

I have a table of clients (with key ClientID) and a table that matches
employees with the clients that they work with (includes EmployeeID
and ClientID as fields). I'd like to write a query that delivers a
list of ALL the clients along with a boolean field that is true if the
employee works with that client.

Example: Clients are 1, 2, and 3. Employee A works with clients 1
and 3, and employee B works with clients 2 and 3. When I run the
query for employee A I'd like to see something like:

Client OnCase
1 True
2 False
3 True

Any suggestions?

Thanks,

Chip

[DFS]

Chip,

Try:

SELECT C.ClientID as Client, iif(IsNull(CE.ClientID), False, True) as OnCase
FROM Clients C LEFT JOIN ClientsEmployees CE
ON C.ClientID = CE.ClientID;

"Chip" <cg******@hotmail.com> wrote in message
news:86**************************@posting.google.c om...

I cannot figure out how to put the following into practice - any help
is appreciated.

I have a table of clients (with key ClientID) and a table that matches
employees with the clients that they work with (includes EmployeeID
and ClientID as fields). I'd like to write a query that delivers a
list of ALL the clients along with a boolean field that is true if the
employee works with that client.

Example: Clients are 1, 2, and 3. Employee A works with clients 1
and 3, and employee B works with clients 2 and 3. When I run the
query for employee A I'd like to see something like:

Client OnCase
1 True
2 False
3 True

Any suggestions?

Thanks,

Chip