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.
Advertisement
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.