I am trying to figure my sql select statement

Hi @David Fetrow ,

Please try the following solution. It is using XQuery and Quantified Expressions.
And it is generic, meaning if the Organization table has more columns to check like AltNTimekeeper, it will work without any T-SQL modification.

SQL

-- DDL and sample data population, start  
DECLARE @employee TABLE (id INT PRIMARY KEY, employeeID INT);  
INSERT INTO @employee (id, employeeID) VALUES  
(1, 3456),  
(2, 5689);  

DECLARE @Organization TABLE (id INT PRIMARY KEY, timekeeper INT, Alt1Timekeeper INT, Alt2Timekeeper INT);  
INSERT INTO @Organization (id, timekeeper, Alt1Timekeeper, Alt2Timekeeper) VALUES  
(1, 1, 23,45),  
(2,33,1, 66),  
(3, 67, 55, 1),  
(4, 57, 77, 88),  
(5, 1, 62, 74);  
-- DDL and sample data population, end  
  
DECLARE @employeeID INT = 3456;  
DECLARE @id INT = (SELECT id FROM @employee WHERE employeeID = @employeeID);  
  
;WITH rs AS  
(  
SELECT *, (  
 SELECT * FROM @Organization AS c  
 WHERE c.id = p.id  
 FOR XML PATH('r'), TYPE, ROOT('root')).value('some $r in /root/r/*[local-name()!=("id")]/text()  
            satisfies $r = sql:variable("@id")', 'BIT') AS Result  
 FROM @Organization AS p  
)  
SELECT * FROM rs  
WHERE Result = 1;  

DECLARE @employeeID int = 3456;
DECLARE @id int;
SELECT @id = id FROM Employee WHERE EmployeeID = @employeeID;
SELECT * 
FROM Organization
WHERE id = @id OR timekeeper = @id OR Alt1Timekeeper = @id OR Alt2Timekeeper = @id;

Hi @David Fetrow ,

According to my understanding, I have tried the following, if this does not solve your problem,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

Please refer to:

    declare @employee table(id int, employeeID int)  
    insert into @employee values(1,3456),(2,5689)   
    declare @Organization table(id int, timekeeper int, Alt1Timekeeper int, Alt2Timekeeper int)  
    insert into @Organization values(1, 1, 23,45),(2,33,1, 66),(3, 67, 55, 1)  
                                   ,(4, 57, 77, 88),(5, 1, 62, 74)   
    ;with cte  
    as(select e.id,e.employeeID,o.id id2,o.timekeeper,o.Alt1Timekeeper,o.Alt2Timekeeper from @employee e  
    full join @Organization o  
    on e.id=o.id)  
      
    select id2 id,timekeeper,Alt1Timekeeper,Alt2Timekeeper from cte  
    where timekeeper=1   
    union all  
    select id2 id,timekeeper,Alt1Timekeeper,Alt2Timekeeper from cte  
    where Alt1Timekeeper=1  
    union all  
    select id2 id,timekeeper,Alt1Timekeeper,Alt2Timekeeper from cte  
    where Alt2Timekeeper=1  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table