-1

How to get table2 records that don't exists in table1 (date filtered)

SELECT e.event_id, e.start_time, te.* 
FROM table1 te, table2_tmp e 
WHERE te.book_id = 45
 AND (DATE(STR_TO_DATE(e.start_time,"%Y%m%d%H%i%s")) BETWEEN DATE(DATE_SUB(NOW(),INTERVAL 1 YEAR)) AND CURRENT_DATE)

table1

ID      event_id  start_time      book_id
10000   1_1005    20191222135100  45  <-- date Filtered & Doesn't exists in t1
10001   1_1006    20200123139100  45
10002   1_1007    20200124145100  45
10003   1_1010    20200125135100  45
10004   1_1015    20200325135100  46

table2

ID      bk_event_id  
11111   1_1006   
11112   1_1007         
11113   1_1015
11113   1_1016

Expected result

ID      event_id  
10000   1_1016
Datacrawler
  • 2,680
  • 7
  • 42
  • 95
Fury
  • 4,395
  • 4
  • 43
  • 77

2 Answers2

0

hi you can use outer join: see this example

this select all of the teams that do not have players in the PLAYERS table. 

SELECT t.*,p.* FROM teams t
              LEFT OUTER JOIN players p ON p.team_id = t.team_id
WHERE p.team_id is NULL;

Here is a good example

Micha
  • 868
  • 4
  • 8
  • This doesn't worked I have tried it before – Fury Jan 24 '20 at 10:55
  • To explain this: you need a join to get a result with more rows on one side, on the other side (second table) there will be null-elements if there ist no matching element. You can select those elements by checking on Null ! Try at first an easy example. – Micha Jan 24 '20 at 11:18
  • When you join it you say `p.team_id = t.team_id` by doing this you ignoring the NULL fields. That's why in result you will get the field that actually matches the second table – Fury Jan 24 '20 at 11:59
0

You can use NOT IN, which you can exclude data from one table to other. I think data you defined in 2 tables and expected result is incorrect.

you can try this query with adding your conditions.

SELECT t2.* from table2 t2 
where bk_event_id NOT IN (SELECT t1.event_id from table1 t1)
Test User
  • 1
  • 2
  • I have tried this but it won't work. Bear in mind the subquery data that (you have excluded by using NOT IN) is the data that we want – Fury Jan 24 '20 at 12:01
  • I have updated query try SELECT t2.* from table2 t2 where bk_event_id NOT IN (SELECT t1.event_id from table1 t1) – Test User Jan 24 '20 at 12:58