0

I am currently working on a little project. I have 2 tables AREA and ACTIVITY

table AREA

+---------------+
| a_id  a_name  |
+---------------+
| 1     area1   |
| 2     area2   |
| 3     area3   |
| 4     area4   |
+---------------+

table ACTIVITY

+-----------------------------------------------------+
| id  areaid  starttime            endtime            |
+-----------------------------------------------------+
| 1   1       2018-09-21 15:00:00 2018-09-21 17:00:00 |
| 2   2       2018-09-23 15:00:00 2018-09-21 18:00:00 |
| 3   1       2018-09-21 17:00:00 2018-09-21 19:00:00 |
| 4   4       2018-07-21 12:00:00 2018-07-21 13:30:00 |
+-----------------------------------------------------+

Now I want to select all areas and join the activity which is currently active or will be next started:

starttime >= NOW() AND endtime >= NOW()
starttime <= NOW() AND endtime >=NOW()

Also the areas with no active or upcoming activity should be shown.

I expect following result:

+-------------------------------------------------------+
| a_id     id   startime            endtime             |
+-------------------------------------------------------+
| 1        1    2018-09-21 15:00:00 2018-09-21 17:00:00 |
| 2        2    2018-09-23 15:00:00 2018-09-21 18:00:00 |
| 3        null null                null                |
| 4        null null                null                |
+-------------------------------------------------------+

I have tried several queries but I did not find the solution yet.

Currently this was my Best try:

SELECT *
FROM areas ar
LEFT JOIN activity a 
ON ar.a_id = a.areaid
WHERE  a.starttime >= NOW() AND a.endtime >= NOW() OR a.starttime <= NOW() AND a.endtime >= NOW()
ORDER BY a.starttime DESC;

I with this query I am able to find all results with active or upcoming activities. But how can I Limit the WHERE expression to just get one row for each foreign key?

Thank you in advance!

Warzulus
  • 46
  • 5
  • I don't have time to go into more detail, but one way is to put another left join to a subquery between the two; the subquery will be used to collect the information identifying the right hand rows for the left hand table, and then link them. Subquery something like... `SELECT a_id, MIN(starttime) FROM activity WHERE yourcriteria GROUP BY a_id` – Uueerdo Aug 31 '18 at 00:09
  • Why is it null for `a_id = 4`? Isn't activity 4 currently active? – Barmar Aug 31 '18 at 00:44
  • After you do your join to get all the rows for an area, use one of techniques in https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 to get the one with the earliest start time. – Barmar Aug 31 '18 at 00:46
  • You also need to move the conditions on the `activity` table into the `ON` clause. In the `WHERE` clause it will filter out any rows that have no match, because `starttime` is `NULL`. See https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 – Barmar Aug 31 '18 at 00:47
  • @Barmar yes a_id = 4 was currently active I edited the post to correct it - so the activity is now in the past. Thanks for your links - I think they will help – Warzulus Aug 31 '18 at 14:57

0 Answers0