T-SQl Query Problem

Question

I have a table called CorporateTree and Production and the tables have data like:

Table: CorporateTree

DivisionName     RegionName         CommonName         BU
Central         Region 1             Raintree           101
Central         Region 1             Glenwood           102
East             Region 2           Balsa              201
East             Region2            Warren             202

Table: Production

ProdID     BU    ResidentName  ResidentID       Room
1           101   Smith, Jeff   234859           1002-A
2           202   Mill, Steve   125467           2002-B
3           101   Sisk, Paul    4383943          1009-C
4           101   Sims, Gary    384393           1010-A
5           202   Mason, Sam    32902            1012-A

I am looking to get output like this:

Division      Region    Facility    Business Unit   ResidentCount   Status
Central      Region 1  Glenwood 102                 0            Flag
Central      Region 1  Raintree 101                 3    
East          Region 2  Balsa      201                 0            Flag
East          Region 2  Warren     202                 2     

if the Number of Residents is zero (0) output the value of “Flag” in a Status

i tried this query:

SELECT ct.DivisionName,ct.RegionName,ct.CommonName AS Facility,ct.BU AS [Business Unit],
(SELECT ROW_NUMBER() OVER (PARTITION BY p.BU ORDER BY p.BU DESC)) AS ResidentCount FROM 
CorporateTree ct INNER JOIN Production p ON 
 p.Bu = ct.BU

But it doesn't seem to be working? Can any one help me on this?

Answer 1

You'll want to use a LEFT JOIN to account for any Business Units that have no rows in the Production table.

SELECT ct.DivisionName, ct.RegionName, ct.CommonName AS Facility, ct.BU AS [Business Unit],
       COUNT(p.BU) as ResidentCount,
       CASE WHEN COUNT(p.BU) = 0 THEN 'Flag' ELSE '' END AS Status
    FROM CorporateTree ct 
        LEFT JOIN Production p 
            ON p.BU = ct.BU
    GROUP BY ct.DivisionName, ct.RegionName, ct.CommonName, ct.BU