Display values from two tables with multiple conditions table1                      


 id | course                                             
----------               
 1  | A                       
 2  | B                       
 3  | C                       
 4  | D                      


table2


id | course |     date    | flag
-------------------------------
1  |    A   | 02-10-2020  | 1
2  |    A   | 03-10-2020  | 0
3  |    B   | 21-01-2019  | 1
4  |    C   | 12-12-2001  | 1
5  |    A   | 30-12-2019  | 1
6  |    C   | 02-10-2019  | 0
7  |    A   | 01-11-2019  | 1



I want output to be like


 course | Date
------------------------
    B   | 21-01-2019
    A   | 01-11-2019


The conditions are 
1) only the courses of table1 that are present in table 2
2) date of the courses should be greater than today
3) only the courses with flag 1 has to be shown
4) choose the closest date to today when there are multiple flag=1 entries
5) output has to be in order by date asc
This should work:

SELECT table2.course, MIN(table2.date)
  FROM table1 INNER JOIN table2
    ON table1.course = table2.course
  WHERE table2.flag = 1
    AND table2.date &gt; NOW()
  GROUP BY table2.course
  ORDER BY MIN(table2.date)


For your current dataset, you don't even need to JOIN with table1.