An elegant way of filtering

agepeder 271 Reputation points
2021-01-24T21:30:34.653+00:00

Hi,

I cant come up with an elegant way of filtering off data in table2 based on the data in table1.
Row3 in table2 needs to be filtered off the rest needs to be kept.

59954-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.2K Reputation points
    2021-01-24T22:08:56.833+00:00

    Had you provided CREATE TABLE + INSERT statements for the sample data, I would have tested my query. This is untested.

    SELECT ...
    FROM  Table2 T2
    WHERE EXISTS (SELECT *
                  FROM   Table1 T1
                  WHERE  T2.Type = T1.Type
                    AND  T2.Name = T1.Name)
    UNION ALL
    SELECT ...
    FROM  Table2 T2
    WHERE NOT EXISTS (SELECT *
                      FROM   Table1 T1
                      WHERE  T2.Type = T1.Type)
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-01-25T02:43:47.673+00:00

    Hi @agepeder ,

    Welcome to Microsoft Q&A!

    Please also refer below method and check whether it is working to you.

    select a.* from Table2 a  
    left join Table1 b   
    on a.Type=b.Type and a.Name=b.Name  
    where b.Type is not null   
    union all  
    select a.* from Table2 a  
    left join Table1 b   
    on a.Type=b.Type   
    where b.Type is null   
    

    Output:

    Type	Name  
    1	Name1  
    1	Name2  
    2	Name4  
    2	Name5  
    2	Name6  
    3	Name7  
    3	Name8  
    3	Name9  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments