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

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

1 Answer 1

Reset to default
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.

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.