-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,780
  • 8
  • 46
  • 100
Fury
  • 4,643
  • 5
  • 50
  • 80

1 Answers1

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
  • 906
  • 6
  • 9
  • 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