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

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

CodePudding user response：

You must do the whole join to discover if some of the results should be removed. To reuse the query results, use a CTE and refer to it twice - once for the results and again to filter out the unwanted rows:

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)

BTW, you have a bug in your query: You're joining [dbo].[TableA] with itself, but it should be joined to [dbo].[TableB]

CodePudding user response：

You can use EXCEPT operator to get your expected result. I will explain my query in step by step process.

Step 1: I am finding out non-matching rows using Except operator:-

    SELECT A.sk, A.class, A.[Values] FROM TableA A

    EXCEPT

    SELECT B.sk, B.class, B.[Values] FROM TableB B

Step 2: Now I am selecting the sk of non-matching rows using:-

 SELECT T.sk FROM (query of step 1)T

Step 3: I am excluding those sk from TableA using:-

SELECT * FROM TableA
WHERE sk NOT IN(query of step 2)

So our final Query is look like this:

 SELECT * FROM TableA
 WHERE sk NOT IN
 (
     SELECT T.sk FROM 
     (
        SELECT A.sk, A.class, A.[Values] FROM TableA A

        EXCEPT

        SELECT B.sk, B.class, B.[Values] FROM TableB B
     )T
 )

CodePudding user response：

As you have used LEFT JOIN your query fetches all the records from the first table, with the matching parts from the second table. If you using simply JOIN the query will only return the records which match.

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