SQL - Accessing data in 2 tables

Question

I have two tables (table A and table B) that have a 1 to many mapping. For every record in table A, I want to check if any of its events in table B occur after 2010. For example:

      Table A                          Table B
      ID     REGISTER                  ID     DATE
      A       qwer                     A      1995-01-01
      B       ghlk                     A      1997-01-31
      C       thasdj                   A      2006-03-15
                                       B      2001-03-15
                                       B      2003-04-03
                                       B      2021-08-01
                                       B      1995-01-01
                                       C      2001-01-01
                                       C      2010-01-01

Therefore, the resulting Table would be

          Table C
        ID    Register
        A       qwer
        C       thasdj

Because for ID A and C, none of their events happens after 2010.

THis is the script I tried using but I'm not sure why it's not working. Any help

        SELECT *
        INTO Table C
        FROM Table A
        where ID not in( 
        
        SELECT distinct ID from Table B
        where [DATE] >= 2011-01-01
                                      

Answer 1

you can do it with insert into {tablename} (list column) select syntax

INSERT INTO C ( ID, Register )
SELECT  A.ID, A.Register
FROM    A  
WHERE A.ID not in ( 
        SELECT distinct ID from Table B
        where [DATE] >= 2011-01-01
)

Answer 2

You can use not exists for this task. Presumably your example query is contrived however note you must properly delimit object names that contain spaces, are reserved words etc and a date value must be quoted.

select *
into TableC
from TableA a
where not exists (
  select * from TableB b 
  where b.Id = a.Id and b.[Date] >='20110101'
);