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

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

1 Answer 1

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.

5
  • 1
    I tested the code on sqlfiddle and its working fine...
    – dwk279
    Sep 13, 2018 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, 2018 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, 2018 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, 2018 at 11:56
  • here is the working link to sqlfiddle demo sqlfiddle.com/#!9/d9bc1/2
    – dwk279
    Sep 13, 2018 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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