ID which is available in table1 but not available in table2, check it date wise [closed]

Question: ID which is available in table1 but not available in table2, check it date wise. Need query in SQL.

Example: Table1:

date	id
1-1-2020	1234
1-1-2020	5678
1-1-2020	6789
2-1-2020	567

Table2:

date	id
1-1-2020	‘b43v1234
1-1-2020	5678vgb’
2-1-2020	‘b43v1234

Result need:

date	id
1-1-2020	6789
2-1-2020	567

I tried

    Select table1.id, table1.date
    from table1
    left join table2 on table1.date = table2.date
        and table2.id like concat('%',table1.id,'%')
    where table2.id is null;

But not getting correct results. It’s not considering this type of match b43v1234 means if 1234 in table 1 and in table2 b43v1234 is available in same date means it’s match so I don’t want to return 1234 but 1234 is also coming in result.

Answer

As Charlieface indicates it appears you want a not exists. The following returns your desired results.

declare @Table1 table ([date] date, id int);

insert into @Table1 ([date], id)
values
('2020-01-01',1234),
('2020-01-01',5678),
('2020-01-01',6789),
('2020-01-02',567);

declare @Table2 table ([date] date, id varchar(8));

insert into @Table2 ([date], id)
values
('2020-01-01','b43v1234'),
('2020-01-01','5678vgb'),
('2020-01-02','b43v1234');

select t1.id, t1.[date]
from @Table1 t1
where not exists (
    select 1
    from @Table2 t2
    where t1.[date] = t2.[date]
    and t2.id like concat('%',t1.id,'%')
);

id	date
6789	2020-01-01
567	2020-01-02

Note: I recommend in future setting up all your questions in this way, with DDL+DML to create the sample data, your attempt, and tabular results.

Also best to use an unambigious date format as you never know what country (and therefore date format) people answers might be in.