title: SQL Check to see if any value in a list exists in another table
body: <p>I have a temp table that looks something like this:</p>
  <pre><code>Record  DepartmentId     PositionId   EmployeeId  StatusId    CustomerId
    1        Null            Null         Null        4  
    2         7              454          Null        Null
    3        Null            454          Null        3
    3        Null            Null         Null        Null        214
    3        Null            Null         Null        Null        100
    3        Null            Null         Null        Null        312
    4        Null            Null         Null        Null        357
  </code></pre>
  <p>I inserted the above into the temp table from tables that looked like this:</p>
  <pre><code>Record Table      Record-to-Department      Record-to-Position
  Record  Name       Record   DepartmentId    Record    PositionId
  1       Red          2          7             2          454
  2       blue                                  3          454
  3       Green
  4       Purple
  
  Record-To-Status             Record-To-Customer
  Record    StatusId           Record    CustomerId
  1         4                    3          214
  3         3                    3          100
                                 3          312
                                 4          357
  </code></pre>
  <p>I have an employee whose record looks something like this:</p>
  <pre><code>   EmployeeId DepartmentId   PositionId   StatusId
         342         7           454           4
  
  Employee Customers:
  EmployeeId   CustomerId
   342             357
   342             95
   342             720
  </code></pre>
  <p>In this scenario, it would return Record 1 (because it matches the StatusId), Record 2 (because it matches both the DepartmentId and the PositionId), but it would not return Record 3 because it only matches the PositionId and not the StatusId, and it would return RecordId 4 because one of the Employee CustomerIds matches the CustomerId on Record 4.</p>
  <p>I got part of this answer on another question <a href="https://stackoverflow.com/questions/67372582/sql-server-compare-1-result-set-against-another-to-return-matches-are-fields-tha">enter link description here</a> (please forgive me I am new and trying to figure out how to ask everything I need to know), but I can't figure out how to handle the multi-records.</p>
  <p>I tried selecting the Employees customer Id's into a table variable and then attempted to use the Coalesce like this:</p>
  <pre><code>Declare @Customers table(CustomerId int)
  INSERT INTO @Customers(CustomerId)
  SELECT DISTINCT S.CustomerId 
  FROM employee_Customers
  
  
  Select * from tbl
  WHERE
  COAlesce(StatusId,@StatusId)=@StatusId AND
  COALESCE(DepartmentId,@DepartmentId)=@DepartmentId AND
  Coalesce(PositionId,@PositionId)=@PositionId AND
  Coalesce(EmployeeCompanyId,@EmployeeCompanyId) = @EmployeeCompanyId AND
  COALESCE((Select CustomerId from tbl_Requirement_to_Customer),(Select CustomerId from @Customers)) = (Select CustomerId from @Customers)
  </code></pre>
  <p>But I receive the error &quot;Subquery Returned more than 1 value&quot;.</p>