Home How to exclude data if any record is not matching in any of the column in the another Table

Questions

How to exclude data if any record is not matching in any of the column in the another Table

February 21, 2022

I have Table A and Table B

CREATE TABLE [dbo].[TableA]
(
    [ID] Int NULL,
    [sk] bigint NULL,
    [class] int NULL,
    [Values] int NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TableA] ([ID], [sk], [class], [Values])
VALUES (1, 17734, 5, 66443), (2, 17734, 4, 5456), 
       (3, 17734, 6, 445645), (4, 17734, 7, 4534),
       (5, 16601, 4, 5443), (6, 16601, 7, 453434), 
       (7, 16601, 8, 76645), (8, 16601, 5, 9875)


CREATE TABLE [dbo].[TableB]
(
    [ID] Int NULL,
    [sk] bigint NULL,
    [class] int NULL,
    [Values] int NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TableB] ([ID], [sk], [class], [Values])
VALUES (1, 17734, 5, 66443), (2, 17734, 4, 5456),
       (3, 17734, 6, 445645), (4, 17734, 7, 4534),
       (5, 16601, 4, 5443), (6, 16601, 7, 453434),
       (7, 16601, 8, 76645), (8, 16601, 5, 9875)

I’m looking to join both the tables with all columns in each table. If any record is not matching then we need to remove all the SK.

For 17734 value all the columns from the both tables are matching then I need to get the values for 17734 .

For 16601 value only 1 value is not matching so I don’t want to bring all the values for 16601.

SELECT DISTINCT 
    DC.[sk], 
    DC.class,
    DC.Values,
    DB.class AS DCC,
    DB.Values AS DBC
FROM
    [dbo].[TableA]
LEFT JOIN 
    [dbo].[TableA] DB ON DC.[sk] = DB.[sk] 
                      AND DC.class = DB.class
                      AND DC.Values = DB.Values;

After joining, I get:

 sk   class Values  class values
--------------------------------
16601   3   65567   NULL NULL
16601   4   5443    4   5443
16601   7   453434  7   453434
16601   8   76645   8   76645
17734   4   5456    4   5456
17734   5   66443   5   66443
17734   6   445645  6   445645
17734   7   4534    7   4534

Output :

 sk   class Values  class values
 --------------------------------
17734   4   5456    4   5456
17734   5   66443   5   66443
17734   6   445645  6   445645
17734   7   4534    7   4534

>Solution :

Use a CTE:

with table1 as (
    select distinct 
        DC.sk, 
        DC.class,
        DC.Values,
        DB.class AS DCC,
        DB.Values AS DBC
    from [dbo].[TableA]
    left join [dbo].[TableB] DB on DC.[sk] = DB.[sk] 
        and DC.class = DB.class
        and DC.Values = DB.Values
)
select *
from table1
where sk not in (select sk from table1 where DCC is null)

You have a bug in your query: You’re joining [dbo].[TableA] with itself, not [dbo].[TableB]