Select rows in one table, based on column values in another table?

Question

I need to select one column for each matching row in another table. Sounds simple, but there's a twist.

I have one table that has Company IDs, and defines Companies.
I have another table that defines Departments in those companies.
I have a third table that contains the various Department StatusCodes.

The code that I have below works just fine, and is exactly what I want it to do. But I have to hardcode into the query which departments to look for. I'd like it to sub-select based on department codes it finds in the Departments table, and not require me to hard code each possible department that might exist at that company.

I need to select the matching department status from DepartmentStatus based upon what Departments exist for a company as defined in DepartmentsStatus table.

I suspect it's a pivot table, but they are a bit above my level.

(Company Table)

Company_ID  Company_Name
-------------------------
1       Home Office
2       Stanton Office
3       NYC Office

(Departments Table)

CompanyID   Department_Code
----------------------------
1           Sales
1           Inventory
1           Retail
1           Maint

2           OtherDept
2           ThatDept
2           BobsDept

(DepartmentStatus Table)

Company_ID  Department  StatusCode
-----------------------------------------
1           Sales       InReview
1           Inventory   InReview
1           Retail      Ready
1           Maint       Done
2           OtherDept   InReview
2           ThatDept    Research
2           BobsDept    InReview

Note: I use "TOP 1", even though there is a unique index on Company_ID+Department, so there will never be more than one matching row.

So, for Company_ID=1:

select  Company_ID,  
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Sales') as SalesStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Inventory') as InvStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Retail') as RetailStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Main') as MaintStatus
from    Company cm
Where   cm.CompanyID=1

Results:

Company_ID      SalesStatus     InvStatus   RetailStatus    MaintStatus
--------------- --------------- ----------  -------------   ------------
1               InReview        InReview    Ready           Done

Or, for CompanyID=2:

select  Company_ID,  
        (select top 1 StatusCode from DepartmentStatus ds where ds.CompanyID = cm.Company_ID and ds.Department='OtherDept') as OtherDeptStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.CompanyID = cm.Company_ID and ds.Department='ThatDept') as ThatDeptStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.CompanyID = cm.Company_ID and ds.Department='BobsDept') as BobsDeptStatus
from    Company cm
Where   cm.CompanyID=2

Results:

Company_ID  OtherDeptStatus     ThatDeptStatus      BobsDeptStatus
----------  ----------------    --------------      --------------
2           InReview            Research            InReview

Thus, For Company 1, I need to go get the status for Departments Sales, Inventory, Retail, and Maint. But for Company 2, I need to get the status for Departments OtherDept, ThatDept, and BobsDept.

The steps that I think describe what I am trying to do is:

Get list of Departments for Company 1 from Departments Table.
For each of these Departments in Step 1, query DepartmentStatus table for Department equals that department. For Company 1, query get StatusCode for Departments "Sales, Inventory, Retail, and Maint" For Company 2, query get StatusCode for Departments "OtherDept, thatDept, and BobsDept." etc etc

The problem is, I do not know ahead of time (at query time) which departments exist for each Company, so I need to sub-select based upon what departments actually exist for each company.

There are a few other S.O. answers already that are close what I'm asking for, suggesting the use of a JOIN to accomplish this, however, they all assume that you know the values you want to query ahead of time when writing the join statement.

I'm looking for a solution to this problem, not just a correction on my current attempt. If you have a better idea entirely on how to accomplish this, I'd love to see it.

user_10676716 · Answer

You seem to be looking for conditional aggregation. Using this technique, your query can be simplified as follows, to avoid the need for multiple inline subqueries:

select  Company_ID,  
        max(case when ds.Department='Sales' then ds.StatusCode end) as SalesStatus,
        max(case when ds.Department='Inventory' then ds.StatusCode end) as InvStatus,
        max(case when ds.Department='Retail' then ds.StatusCode end) as RetailStatus,
        max(case when ds.Department='Main' then ds.StatusCode end) as MaintStatus
from    
    Company cm
    inner join DepartmentStatus ds on ds.Company_ID = cm.Company_ID 
Where   cm.CompanyID=1
group by cm.CompanyID

user_12053273 · Answer

this would be my (untested) caveman way of solving your problem. the only thing you need to know in advance is how many different departments you have in the company with the most departments.

DROP TABLE IF EXISTS Iddepartmentstatus;

DECLARE @Result TABLE(
                      Companyid   INT, 
                      Department1 NVARCHAR(MAX), 
                      Department2 NVARCHAR(MAX), 
                      Department3 NVARCHAR(MAX), 
                      Department4 NVARCHAR(MAX), 
                      Department5 NVARCHAR(MAX));

CREATE TABLE Iddepartmentstatus(
             Num        INT IDENTITY(1, 1), 
             Department NVARCHAR(MAX), 
             Statuscode NVARCHAR(MAX));

DECLARE @IDCompany TABLE(
                         Num         INT IDENTITY(1, 1), 
                         Companyid   INT, 
                         Companyname NVARCHAR(MAX));

INSERT INTO            @IDCompany (Companyid, 
                                   Companyname) 
       SELECT Company_Id, 
              Company_Name
       FROM   Company;

DECLARE @Departmentcount       INT = 0, 
        @Departmentstatuscount INT = 0, 
        @Companycount          INT = 0;

WHILE @Companycount <=
(
    SELECT MAX(Num)
    FROM   @IDCompany)
    BEGIN
        INSERT INTO                    Iddepartmentstatus (Department, 
                                                           Statuscode) 
               SELECT Department, 
                      Statuscode
               FROM   Departmentstatus
               WHERE  Company_Id = @Companycount;

        INSERT INTO         @Result (Companyid) 
               SELECT @Companycount AS T;

        INSERT INTO         @Result (Department1) 
               SELECT   IIF(1 <=
               (
                   SELECT MAX(Num)
                   FROM   @IDCompany), Department + ': ' + Departmentstatus, NULL)
               FROM Iddepartmentstatus
               WHERE    Num = 1;

        INSERT INTO         @Result (Department2) 
               SELECT   IIF(2 <=
               (
                   SELECT MAX(Num)
                   FROM   @IDCompany), Department + ': ' + Departmentstatus, NULL)
               FROM Iddepartmentstatus
               WHERE    Num = 2;

        INSERT INTO         @Result (Department3) 
               SELECT   IIF(3 <=
               (
                   SELECT MAX(Num)
                   FROM   @IDCompany), Department + ': ' + Departmentstatus, NULL)
               FROM Iddepartmentstatus
               WHERE    Num = 3;

        INSERT INTO         @Result (Department4) 
               SELECT   IIF(4 <=
               (
                   SELECT MAX(Num)
                   FROM   @IDCompany), Department + ': ' + Departmentstatus, NULL)
               FROM Iddepartmentstatus
               WHERE    Num = 4;

        INSERT INTO         @Result (Department5) 
               SELECT   IIF(5 <=
               (
                   SELECT MAX(Num)
                   FROM   @IDCompany), Department + ': ' + Departmentstatus, NULL)
               FROM Iddepartmentstatus
               WHERE    Num = 5;

        TRUNCATE TABLE Iddepartmentstatus;
        SET @Companycount = @Companycount + 1;
    END;
DROP TABLE IF EXISTS Iddepartmentstatus;

Select rows in one table, based on column values in another table?

2 Answers