sqljoin

Get the non-matching rows in SQL


We have two SQL tables

table_1 and table_2.

Now, We have to do a merger of these two table in such a way that, table_1 seq_number which is not present in table_2, map with the tp_no = 0 Like this.

output

We have to create this in SQL table so it could be used further.

I am trying to achieve this by:

select distinct tar.c_Id,brd.Toucpoint,brd.sequence_number,tar.tot_Id,tar.tp_no
from table_2 tar 
inner join (
     select distinct c_Id,channel,sequence_number
     from table_1
) brd on tar.c_Id = brd.c_Id and tar.tp_no <> brd.sequence_number
where tar.tp_no = '0';

But this is not giving the desired output.


Solution

  • I used a CTE named SeqNotInTp_no to first select the sequences from Table_1 that do not exist as Tp_no in Table_2. I then joined SeqNotInTp_no with Tabel_2

    WITH  SeqNotInTp_no as
    (
      SELECT * FROM Table_1 WHERE Seq_Number NOT IN (SELECT tp_no FROM Table_2  WHERE Table_1.C_Id=Table_2.C_Id)  
    )
    SELECT SeqNotInTp_no.*,t2.Tot_Id, t2.Tp_no
    FROM SeqNotInTp_no 
    INNER JOIN Table_2 t2 ON t2.C_Id=SeqNotInTp_no.C_Id   
    WHERE t2.Tp_no='0'  
    

    fiddle

    C_id Toucpoint Seq_Number Tot_Id Tp_no
    Camp_1 F 1 388 0
    Camp_1 Email 3 388 0