1

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

  • 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. – Akina Sep 13 '18 at 9:12
  • Only records with flag=1 – dwk279 Sep 13 '18 at 9:24
  • 1
    If so @Glorfindel's answer is a solution. – Akina Sep 13 '18 at 9:26
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.

  • 1
    I tested the code on sqlfiddle and its working fine... – dwk279 Sep 13 '18 at 11:54
  • 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 ...... – dwk279 Sep 13 '18 at 11:54
  • 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 Sep 13 '18 at 11:55
  • Your new error is a PHP problem, which is more suitable for Stack Overflow (but please check the existing questions first). – Glorfindel Sep 13 '18 at 11:56
  • here is the working link to sqlfiddle demo sqlfiddle.com/#!9/d9bc1/2 – dwk279 Sep 13 '18 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.