T-SQL - calculate a boolean field on the fly

Question

I'm using SQL Server 2008.

Let's say I have two hypothetical tables like below:

CREATE TABLE [Department](
    [Id]            int    IDENTITY(1,1),
    [ManagerId]     int    NULL, -- << Foreign key to the Person table
    -- other fields
)

CREATE TABLE [Person](
    [Id]            int    IDENTITY(1,1),
    [DepartmentId]  int    NOT NULL, -- << Foreign key to the Department table
    -- other fields
)

Now, I want to return a list of rows from the [Person] table (i.e. list of staff for a given department). Only one (or zero) of these rows will match the [ManagerId] field in the [Department] table. And I want to flag the matched row with a boolean field on the fly... the resultant rowset will resemble the following schema:

[Id]        INT,
[IsManager] BIT NOT NULL DEFAULT 0,
-- other fields

The [IsManager] field will be TRUE when [Department].[ManagerId] matches [Person].[Id].

This is fairly trivial to do with two (or more) queries. But how can I achieve this using a single SQL statement?

[A `bit` field isn't a `boolean` field](http://stackoverflow.com/questions/11135134/sql-server-boolean-operators) (just saying). — Andriy M, Jun 26 '12 at 07:18

rabudde · Accepted Answer · 2012-06-26T07:15:10.657

4

Add an expression to your SELECT clause where you compare actual persons Id with ManagerId from persons department

SELECT
    Person.Id,
    Department.Id,
    CAST(CASE WHEN Person.Id=Department.ManagerId THEN 1 ELSE 0 END AS BIT) AS IsManager
FROM Person
INNER JOIN Department ON Person.DepartmentId=Department.Id
WHERE Person.DepartmentId=<CONDITION>

edited Jun 26 '12 at 07:15

answered Jun 26 '12 at 06:58

rabudde

7,498
6
53
91

Peter · Answer 2 · 2012-06-26T15:32:24.373

2

A left join from the Person table to the department table on ManagerId will do the trick for you:

SELECT p.Id AS PersonId, d.Id AS DepartmentId, 
    CAST(CASE WHEN d.Id IS NULL THEN 0 ELSE 1 END) AS IsManager
FROM Person p LEFT JOIN Department d ON p.Id = d.ManagerId

How it works: All rows from Person are return, regardless of the existence of a corresponding Department matching on ManagerId. For those Person records without a matching department, all of the Department fields in the resultset are NULL, so we can use that to determine whether or not there is a match.

Note that this query may return duplicate Person records, if a person is a manager for multiple departments. To this end, I have added the DepartmentId to the list. If you require a unique list of persons and their IsManager flag, drop d.DepartmentId from the select clause and insert DISTINCT after the select:

SELECT DISTINCT p.Id AS PersonId, 
    CAST(CASE WHEN d.DepartmentId IS NULL THEN 0 ELSE 1 END) AS IsManager
FROM Person p LEFT JOIN Department d ON p.Id = d.ManagerId

edited Jun 26 '12 at 15:32

answered Jun 26 '12 at 07:12

Peter

1,055
6
8

Why `LEFT JOIN` on `ManagerId`? Table `Department` is always referenced, so do an `INNER JOIN`. There are no duplicates an also a `DISTINCT` is unnecessary. See me anwer – rabudde Jun 26 '12 at 07:17
Small typos fixed: SELECT p.Id AS PersonId, d.Id AS DepartmentId, CAST(CASE WHEN d.Id IS NULL THEN 0 ELSE 1 END AS BIT) AS IsManager FROM Person p LEFT JOIN Department d ON p.Id = d.ManagerId – invarbrass Jun 26 '12 at 07:41
Thanks, invarbrass - I've edited that. Why the `LEFT JOIN`? Because the question called for all people, flagging those who are managers, and not just those who are managers of their own department. If there is a Senior Management department, where each member of the department is the manager of a different department, then the `INNER JOIN` wouldn't work. However, which solution best solves the problem depends on the structure of the data. – Peter Jun 26 '12 at 15:42

T-SQL - calculate a boolean field on the fly

2 Answers2