Search code examples
sqlsql-serversql-server-2016

check all rows from one table exists on another table

I have table A with columns (candidate_ref, role_num, step, action_date) and table B (candidate_ref, role_num, new_role_num,step, action_date).

enter image description here

Table A has a candidate (ref 231) for role 1. This candidate has been moved to another role for which we have the information in table B. In table B, using columns candidate_ref, role_num and new_role_num we can see that candidate 231 of role 1 is moved to role 7, 9 and 21, however, only move (1 to 7) is a valid as all steps from role 1 (table A) are present in role 7 (table B).

I like to figure out way to find out valid role move. Here move from 1 to 7 is valid move and moves 1 to 9 and 1 to 21 are not.

Example code create test data is given below:

drop table if exists #table_a
create table #table_a
(
candidate_ref int
,role_num int
,step varchar(25)
,action_date datetime
)

drop table if exists #table_b
create table #table_b
(
candidate_ref int
,role_num int
,new_role_num int
,step varchar(25)
,action_date datetime
)

insert into #table_a
select 231, 1,  'New application', '2021-08-17' union
select 231, 1,  'On hold', '2022-02-02' union
select 231, 1,  'Pending reject','2022-02-28' union
select 231, 1,  'Rejection / Not suitable', '2022-02-28' union
select 231, 1,  'Online withdrawal',    '2022-10-24'

insert into #table_b
select 231,1,9,'Candidate withdrawal', '2021-08-07' union 
select 231,1,7,'New application', '2023-03-01' union 
select 231,1,7,'On hold', '2021-08-07' union 
select 231,1,7,'Pending reject', '2022-02-02' union 
select 231,1,7,'Rejection / Not suitable', '2022-02-28' union 
select 231,1,7,'Online withdrawal', '2022-02-28' union 
select 231,1,21,'New application', '2022-11-27' union 
select 231,1,21,'On hold', '2022-11-27'

I have thought about using full outer join but that hasn't worked for me exactly. As an output I would like to have some sort of valid_move flag on table B with 1 against new_role_number 7 and 0 for 9 and 21.

Solution

  • Something like this perhaps?

    select  ISNULL(t.candidate_ref, tb.candidate_ref) AS candidate_ref
, ISNULL(t.role_num, tb.role_num) AS role_num, tb.new_role_num
, ISNULL(t.step, tb.step) AS step, ISNULL(t.action_date, tb.action_date) AS action_date
, case when t.cnt = COUNT(tb.candidate_ref) OVER(PARTITION BY t.candidate_ref, t.role_num, tb.new_role_num) THEN 1 ELSE 0 END AS validCombination
FROM    (
        SELECT  COUNT(*) OVER(PARTITION BY t.candidate_ref, t.role_num) AS cnt
        ,   *
        from    #table_a t
    ) t
FULL JOIN #table_b tb
    ON  tb.candidate_ref = t.candidate_ref
    AND tb.role_num = t.role_num
    AND tb.step = t.step

    I precount number of steps the "real" table has, and then compare it to count of steps when full joining table_b, where step match table_a, grouped by new role. If both side has same number of matching steps, they get a 1, otherwise 0.

    This might fail if there are duplicate steps per new role in table_b. A solution to that is to create a subquery then filters out the duplicates ahead of time.