An elegant way of filtering

Question

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.

Answer 1

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)

Answer 2

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.