How to select rows with related values?

user_11494451 0
Viewed 117

I have a table "company" with data like follows containing employees and their companies:

EmployeeID        EmployeeName           CompanyName            CompanyID           ParentCoID 
------------------------------------------------------------------------------------------------

100                John                      A                       500                 NULL
100                John                      A.1                     600                 500
250                Paul                      B                       800                 NULL
250                Paul                      ABC                     2000                NULL
350                Joe                       D                       1000                5000
600                Tom                       E                       700                 NULL
600                Tom                       E.2                     1500                700

I am trying to display rows where if the Parent Co ID value is in the Company ID, then show both rows for that employee. So basically select employees that are in both the parent company and the sub company and show both of those rows.

I've tried

SELECT *
FROM company
WHERE CompanyID IN (SELECT ParentCoID FROM company)

but this only returns the row where the CompanyID value matches and I'm not sure how to also include that second row also.

My desired output for the sample above would be:

EmployeeID        EmployeeName           CompanyName            CompanyID           ParentCoID 
------------------------------------------------------------------------------------------------
    
100                John                      A                       500                 NULL
100                John                      A.1                     600                 500
600                Tom                       E                       700                 NULL
600                Tom                       E.2                     1500                700

As from the result above, Company A.1 is a sub company of A, and same with company E and E.2. I am trying to select employees that are in both the main company and sub company and therefore need to refer to the ParentCoID and the CompanyID columns.

tsql sql-server sql
5 Answers

Please try this: Updated the query.

SELECT * FROM company c
WHERE CompanyID IN (SELECT ParentCoID FROM company c2 WHERE c2.EmployeeID = c.EmployeeID) OR ParentCoID IN (SELECT CompanyID FROM company c3 WHERE c3.EmployeeID = c.EmployeeID)
user_67132160 
SELECT * FROM company
WHERE EmployeeID IN (SELECT EmployeeID FROM company WHERE CompanyID IN (SELECT ParentCoID FROM company)
user_101342530

Using EXISTS

SELECT a.*
FROM   company a
WHERE  EXISTS (SELECT b.parentcoid
               FROM   company b
               WHERE  a.companyid = b.parentcoid
                      AND a.employeeid = b.employeeid)
UNION ALL
SELECT c.*
FROM   company c
WHERE  EXISTS (SELECT d.companyid
               FROM   company d
               WHERE  d.companyid = c.parentcoid
                      AND d.employeeid = c.employeeid)
user_111561310

A recursive CTE is useful in querying hierarchical data.

with cte As (
select * from company c
Where ParentCoID is null and CompanyID = Any(Select ParentCoID From company Where EmployeeID=c.EmployeeID)
union all
select c.*
from cte p inner join company c On (p.CompanyID=c.ParentCoID And p.EmployeeID=c.EmployeeID)
)
select * From cte order by EmployeeID, CompanyID, ParentCoID
user_117698590

Here's a slightly different approach using a simple window function and a self-join and will likely be performant given a clustered index on (EmployeeId,ParentCoId).

with e as (
    select EmployeeID,ParentCoID CoId, 
        Sum(case when ParentCoID is null then 1 end ) over(partition by EmployeeID) IsSub
    from Company
)
select c.* 
from e 
join Company c on c.EmployeeID=e.EmployeeID
where (e.CoId=c.CompanyID or e.CoId=c.ParentCoID)
and e.CoId is not null and e.IsSub=1
user_153326500
Related