SQL - Accessing data in 2 tables-CodePudding

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

CodePudding user response：

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'
);

CodePudding user response：

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
)