Display values from two tables with multiple conditions

Question

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

• Akina: Only the course with flag 1 has to be shown This means "only records with flag=1" or "only courses which have no any record with flag!=1"? An example makes to think that option 1 is correct.
• dwk279: Only records with flag=1
• Akina: If so @Glorfindel's answer is a solution.

Answer 1

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 > NOW()
 GROUP BY table2.course
 ORDER BY MIN(table2.date)

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

• dwk279: I tested the code on sqlfiddle and its working fine...
• dwk279: but I am getting this warning on my website Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/***/***/*** on line 203 ......
• Glorfindel: Thanks! In the future, it's best to provide the sqlfiddle when posting the question; you'll make it easier for people to answer the question (I had to program this 'in my mind' which is doable for a simple query like this, but not if your problem is more complex).
• Glorfindel: Your new error is a PHP problem, which is more suitable for Stack Overflow (but please check the existing questions first).
• dwk279: here is the working link to sqlfiddle demo sqlfiddle.com/#!9/d9bc1/2